[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor l_start_date_csr(cp_kle_id IN NUMBER, cp_sequence IN NUMBER) IS select start_date from (
select rownum sequence, --this can replace the seq since seq num is not stored by authoring
A.* from
(Select sll.id,
styp.code payment_type,
sll.object1_id1 Pay_freq,
sll.rule_information1 seq,
fnd_date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information3 number_periods,
sll.rule_information4 tuoms_per_period,
sll.rule_information6 amount,
sll.rule_information7 stub_days,
sll.rule_information8 stub_amount,
sll.rule_information10 advance_or_arrears,
sll.rule_information13 rate,
rgp.cle_id cle_id
from okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_v styp,
okc_rule_groups_b rgp
where to_number(sll.object2_id1) = slh.id
and sll.rule_information_category = 'LASLL'
and sll.dnz_chr_id = rgp.dnz_chr_id
and sll.rgp_id = rgp.id
and slh.rule_information_category = 'LASLH'
and slh.dnz_chr_id = rgp.dnz_chr_id
and slh.rgp_id = rgp.id
and slh.object1_id1 = styp.id
and rgp.rgd_code = 'LALEVL'
and rgp.cle_id = cp_kle_id
order by rgp.cle_id
, fnd_date.canonical_to_date(sll.rule_information2)) A
)
where sequence = cp_sequence;
Select fnd_date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information7 stub_days,
sll.rule_information8 stub_amount
from okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_v styp,
okc_rule_groups_b rgp
where to_number(sll.object2_id1) = slh.id
and sll.rule_information_category = 'LASLL'
and sll.dnz_chr_id = rgp.dnz_chr_id
and sll.rgp_id = rgp.id
and slh.rule_information_category = 'LASLH'
and slh.dnz_chr_id = rgp.dnz_chr_id
and slh.rgp_id = rgp.id
and slh.object1_id1 = styp.id
and rgp.rgd_code = 'LALEVL'
and rgp.cle_id = cp_kle_id
and sll.rule_information7 IS NOT NULL
and sll.rule_information8 IS NOT NULL
--and fnd_date.canonical_to_date(sll.rule_information2) <= cp_start_date
order by abs(fnd_date.canonical_to_date(sll.rule_information2) - cp_start_date) asc;
select NVL(sum(sel.amount), 0) amount
from
okl_K_lines_full_v kle,
okc_statuses_b sts,
okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE kle.dnz_chr_id = p_chr_id
AND kle.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND kle.dnz_chr_id = stm.khr_id
AND kle.id = stm.kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND stm.id = sel.stm_id
AND ((p_advance_or_arrears = 'ARREARS' and sel.stream_element_date > p_date)
OR (p_advance_or_arrears <> 'ARREARS' and sel.stream_element_date >= p_date));
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_b sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND nvl( str.purpose_code, 'XXXX' ) = 'XXXX'
AND str.khr_id = p_chr_id
AND str.kle_id = p_kle_id
AND nvl(str.kle_id, -1) = -1
AND ((p_advance_or_arrears = 'ARREARS' and sele.stream_element_date > p_date)
OR (p_advance_or_arrears <> 'ARREARS' and sele.stream_element_date >= p_date));
select NVL(sum(sel.amount), 0) amount
from
okl_K_lines_full_v kle,
okc_statuses_b sts,
okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE kle.dnz_chr_id = p_chr_id
AND kle.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND kle.dnz_chr_id = stm.khr_id
AND kle.id = stm.kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'LEASE_INCOME'
AND stm.id = sel.stm_id
AND sel.stream_element_date >= TRUNC(p_date, 'MONTH');
SELECT nvl(kle.residual_value,0) Value
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.DNZ_CHR_ID = p_chr_id --289326506849179644190030423574805590144
AND KLE.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
Select FA_BOOKS.ASSET_ID ASSET_ID,
FA_BOOKS.cost ASSET_COST,
FA_BOOKS.book_type_code BOOK_TYPE_CODE
from FA_BOOKS ,
FA_BOOK_CONTROLS,
OKC_K_LINES_B LINES,
OKC_LINE_STYLES_B STYLE,
OKC_K_ITEMS KITEM
where FA_BOOKS.asset_id = KITEM.OBJECT1_ID1
and LINES.DNZ_CHR_ID = p_chr_id --291511068054787299132375269533568315520
and LINES.cle_id = p_kle_id
and LINES.ID = KITEM.CLE_ID
and LINES.LSE_ID = STYLE.ID
and STYLE.LTY_CODE = 'FIXED_ASSET'
and FA_BOOKS.book_type_code = FA_BOOK_CONTROLS.book_type_code
and FA_BOOK_CONTROLS.book_class = 'CORPORATE'
and FA_BOOKS.transaction_header_id_out is null;
select sum(deprn_amount) ACCUMULATED_DEPRECIATION
from fa_deprn_summary
where Asset_id = p_asset_id
and book_type_code = p_book_type_code;
SELECT khr.deal_type deal_type
FROM okc_k_headers_b CHR,
okl_k_headers khr
WHERE khr.id = CHR.id
AND khr.id = p_khr_id;
SELECT id
FROM okl_var_int_process_b
WHERE PARENT_TRX_ID = p_trx_id;
select sll_rulb.dnz_chr_id khr_id,
rgpb.cle_id cle_id,
--LASLL values
sll_rulb.rule_information_category sll_rule_information_category,
to_number(sll_rulb.rule_information1) seq, -- 4899594
sll_rulb.rule_information2 start_date,
sll_rulb.rule_information3 number_periods,
sll_rulb.rule_information4 tuoms_per_period,
sll_rulb.object1_id1 Pay_freq,
sll_rulb.rule_information5 structure,
nvl( sll_rulb.rule_information10,'N') advance_or_arrears,
sll_rulb.rule_information6 amount,
sll_rulb.rule_information7 stub_days,
sll_rulb.rule_information8 stub_amount,
sll_rulb.rule_information13 rate,
sll_rulb.jtot_object1_code time_unit_of_measure,
sll_rulb.jtot_object2_code stream_level_header,
--LASLH values
slh_rulb.rule_information_category slh_rule_information_category,
slh_rulb.jtot_object1_code stream_type_source,
slh_rulb.jtot_object2_code time_value,
slh_rulb.object1_id1 sty_id,
slh_rulb.rule_information1 billing_schedule_type,
slh_rulb.rule_information2 rate_type
from okc_rules_b sll_rulb,
okc_rules_b slh_rulb,
okl_strm_type_b styb,
okc_rule_groups_b rgpb
where sll_rulb.rgp_id = rgpb.id
and sll_rulb.rule_information_category = 'LASLL'
and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
and sll_rulb.object2_id1 = to_char(slh_rulb.id)
and slh_rulb.rgp_id = rgpb.id
and slh_rulb.rule_information_category = 'LASLH'
and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
and styb.id = slh_rulb.object1_id1
and styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
and rgpb.dnz_chr_id = p_khr_id
and rgpb.cle_id = p_cle_id
and rgpb.rgd_code = 'LALEVL'
order by to_number(sll_rulb.rule_information1); -- 4899594
select id1 from okl_time_units_v
where name = l_freq;
SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
,currency_code
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
SELECT rul2.object1_id1
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = p_contract_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = p_cle_id
AND sty.id = rul1.object1_id1
AND sty.stream_type_purpose IN ( 'RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT' );
OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
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_contract_id => p_chr_id,
p_last_interest_cal_date => l_next_reamort_date);
OKL_VARIABLE_INTEREST_PVT.print_debug(p_trx_id || 'Before call to okl_var_int_process_b.update_row' );
OKL_VIP_PVT.update_row(
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_vipv_rec => l_vipv_rec,
x_vipv_rec => x_vipv_rec);
l_program_update_date DATE;
SELECT SRLB.REAMORT_BALANCE
FROM OKL_SIF_RET_LEVELS SRLB,
OKL_SIF_RETS SIRB,
OKL_STREAM_INTERFACES SIFB,
OKL_SIF_LINES SILB
WHERE SIFB.TRANSACTION_NUMBER = p_trx_number
AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
AND SIRB.INDEX_NUMBER = 0
AND SILB.SIF_ID = SIFB.ID
AND SRLB.SIR_ID = SIRB.ID
AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
AND SRLB.LEVEL_TYPE IN ('Payment','Principal')
AND SRLB.LOCK_LEVEL_STEP = 'N'
AND SRLB.LEVEL_INDEX_NUMBER = 0
AND SILB.KLE_ID = p_kle_id;
SELECT 'Y'
FROM OKC_K_LINES_B CLE
, OKC_STATUSES_B STS
WHERE CLE.ID = p_kle_id
AND CLE.LSE_ID = 33
AND STS.CODE = CLE.STS_CODE
AND STS.STE_CODE = 'TERMINATED';
SELECT okc.contract_number
, NVL(okl.date_last_interim_interest_cal, okc.start_date) start_date
, okl.deal_type deal_type
, ppm.quality_val interest_calculation_basis
, okc.authoring_org_id
, round(months_between(okc.end_date, okl.date_last_interim_interest_cal)) remaining_term_in_months
FROM okc_k_headers_b okc
, okl_k_headers okl
, okl_prod_qlty_val_uv ppm
WHERE okc.id = cp_khr_id
AND okl.id = okc.id
AND okl.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS';
SELECT (KHR.TERM_DURATION - round(months_between(nvl(p_period_date,K.START_DATE), K.START_DATE))) MTH,
KHR.TERM_DURATION term_duration
FROM OKC_K_HEADERS_B K, OKL_K_HEADERS KHR
WHERE K.id = p_contract_id
and K.id = KHR.id;
SELECT rul2.object1_id1
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = p_contract_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
--AND rgp.cle_id = p_cle_id
AND sty.id = rul1.object1_id1
AND sty.stream_type_purpose IN ( 'RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT' )
ORDER BY decode(rul2.object1_id1,'M', 1, 'Q', 3, 'S', 6, 'A', 12 );
SELECT interest_rate,interest_calc_end_date
FROM okl_var_int_params
WHERE khr_id = p_contract_id
AND INTEREST_CALC_END_DATE = (SELECT max(INTEREST_CALC_END_DATE) FROM okl_var_int_params WHERE khr_id = p_contract_id);
SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT max(transaction_number) max_transaction_number
FROM okl_stream_interfaces
WHERE khr_id = p_contract_id
AND to_number(transaction_number) > p_trx_number
AND orp_code = 'AUTH';
SELECT id
FROM okc_k_lines_b
WHERE chr_id = p_contract_id
AND dnz_chr_id = p_contract_id
AND lse_id = 33
AND sts_code NOT IN('ABANDONED', 'TERMINATED');
l_csm_stream_types_tbl.DELETE;
l_rents_prin_tbl.delete;
l_rents_tbl_in.delete;
l_req_stream_types_tbl.delete;
OKL_VIP_PVT.insert_row(
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_vipv_rec => l_vipv_rec,
x_vipv_rec => x_vipv_rec);
OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
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_contract_id => p_khr_id,
p_last_interest_cal_date => l_next_reamort_date);
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
INTO
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
FROM dual;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_update_date := SYSDATE;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_date := SYSDATE;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).program_update_date := SYSDATE;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_date := SYSDATE;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl(OKL_VARIABLE_INTEREST_PVT.g_vir_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
OKL_VR_DATA_HANDLER_PVT.INSERT_VIR_PARAMS(
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_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_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;