[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_exception_cannot_update EXCEPTION;
SELECT std_rate_tmpl_id
,template_name
,template_desc
,object_version_number
,org_id
,currency_code
,rate_card_yn
,default_yn
,pricing_engine_code
,orig_std_rate_tmpl_id
,frequency_code
,rate_type_code
,index_id
,sts_code
,effective_from_date
,effective_to_date
,srt_rate
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_std_rt_tmp_v
WHERE std_rate_tmpl_id = p_srt_id;
SELECT std_rate_tmpl_ver_id
,version_number
,object_version_number
,std_rate_tmpl_id
,sts_code
,effective_from_date
,effective_to_date
,adj_mat_version_id
,srt_rate
,spread
,day_convention_code
,min_adj_rate
,max_adj_rate
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_version_number;
SELECT criteria_set_id
,object_version_number
,source_id
,source_object_code
,match_criteria_code
,validation_code
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_criteria_set
WHERE source_id = p_source_id AND source_object_code = p_source_object;
SELECT criteria_id
,object_version_number
,match_criteria_code
,criteria_set_id
,crit_cat_def_id
,effective_from_date
,effective_to_date
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_criteria
WHERE criteria_set_id = p_criteria_set_id;
SELECT criterion_value_id
,object_version_number
,criteria_id
,operator_code
,crit_cat_value1
,crit_cat_value2
,adjustment_factor
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_criterion_values
WHERE criteria_id = p_criteria_id;
SELECT data_type_code
,value_type_code
FROM okl_fe_crit_cat_def_v
WHERE crit_cat_def_id = criteria_def_id;
x_ech_rec.last_updated_by := cat_hdr_rec.last_updated_by;
x_ech_rec.last_update_date := cat_hdr_rec.last_update_date;
x_ech_rec.last_update_login := cat_hdr_rec.last_update_login;
x_ecv_tbl(j).last_updated_by := cat_values_rec.last_updated_by;
x_ecv_tbl(j).last_update_date := cat_values_rec.last_update_date;
x_ecv_tbl(j).last_update_login := cat_values_rec.last_update_login;
x_ecl_tbl(i).last_updated_by := cat_lines_rec.last_updated_by;
x_ecl_tbl(i).last_update_date := cat_lines_rec.last_update_date;
x_ecl_tbl(i).last_update_login := cat_lines_rec.last_update_login;
SELECT MAX(version_number)
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id;
PROCEDURE insert_srt(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_srtv_rec IN okl_srtv_rec
,p_srv_rec IN okl_srv_rec
,x_srtv_rec OUT NOCOPY okl_srtv_rec
,x_srv_rec OUT NOCOPY okl_srv_rec) IS
l_srtv_rec okl_srtv_rec := p_srtv_rec;
l_api_name VARCHAR2(40) := 'INSERT_SRT';
SELECT val.value
FROM okl_indices ind
,okl_index_values val
WHERE ind.id = val.idx_id AND ind.id = p_index_id
AND p_eff_from BETWEEN val.datetime_valid AND NVL(val.datetime_invalid, TO_DATE('01-01-9999', 'dd-mm-yyyy'));
SELECT 'x'
FROM okl_fe_std_rt_tmp_v
WHERE template_name = UPPER(p_name);
okl_srt_pvt.insert_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
okl_srv_pvt.insert_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
END insert_srt;
PROCEDURE update_srt(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_srv_rec IN okl_srv_rec
,x_srv_rec OUT NOCOPY okl_srv_rec) IS
l_srtv_rec okl_srtv_rec;
l_api_name VARCHAR2(40) := 'UPDATE_SRT';
SELECT effective_from_date
,effective_to_date
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = l_srt_id
AND version_number = l_version_number - 1;
SELECT max(effective_from_date)
FROM okl_fe_criteria_set ech
,okl_fe_criteria ecl
WHERE ecl.criteria_set_id = ech.criteria_set_id
AND ech.source_id = p_version_id AND source_object_code = 'SRT';
okl_srt_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
okl_srt_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
okl_srv_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
END update_srt;
okl_srt_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
okl_srv_pvt.insert_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
okl_srv_pvt.update_row(l_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
SELECT std_rate_tmpl_id
,version_number
,effective_from_date
,effective_to_date
,srt_rate
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_ver_id = p_version_id;
SELECT std_rate_tmpl_ver_id
,effective_to_date
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_version_number;
SELECT MAX(version_number)
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id;
SELECT value
FROM okl_index_values_v
WHERE idx_id = index_id AND effective_from >= datetime_valid
AND effective_from <= NVL(datetime_invalid, TO_DATE('01-01-9999', 'dd-mm-yyyy'));
SELECT index_id
FROM okl_fe_std_rt_tmp_all_b
WHERE std_rate_tmpl_id = p_srt_id;
okl_srv_pvt.update_row(l_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
okl_srv_pvt.update_row(l_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srv_rec
,x_srv_rec);
okl_srt_pvt.update_row(l_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
PROCEDURE update_default(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_srt_id IN NUMBER) AS
CURSOR default_yn_csr IS
SELECT std_rate_tmpl_id
FROM okl_fe_std_rt_tmp_all_b
WHERE default_yn = 'Y';
l_api_name VARCHAR2(40) := 'update_default';
okl_srt_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
okl_srt_pvt.update_row(l_api_version
,l_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_srtv_rec
,x_srtv_rec);
END update_default;
SELECT vers.rate_set_version_id id
,hdr.name name
,vers.version_number version_number
FROM okl_fe_rate_set_versions vers
,okl_ls_rt_fctr_sets_v hdr
WHERE vers.rate_set_id = hdr.id
AND vers.std_rate_tmpl_ver_id = p_version_id
AND vers.sts_code = 'ACTIVE';
SELECT MAX(lq.expected_start_date) start_date
FROM okl_lease_quotes_b lq
,okl_fe_std_rt_tmp_vers srt
WHERE srt.std_rate_tmpl_ver_id = p_version_id
AND lq.rate_template_id = srt.std_rate_tmpl_ver_id;
SELECT MAX(qq.expected_start_date) start_date
FROM okl_quick_quotes_b qq
,okl_fe_std_rt_tmp_vers srt
WHERE srt.std_rate_tmpl_ver_id = p_version_id
AND qq.rate_template_id = srt.std_rate_tmpl_ver_id;
SELECT MAX(expected_start_date) start_date
FROM okl_lease_quotes_b
WHERE rate_card_id IN(SELECT rate_set_version_id
FROM okl_fe_rate_set_versions
WHERE std_rate_tmpl_ver_id = p_version_id);
SELECT MAX(expected_start_date) start_date
FROM okl_quick_quotes_b
WHERE rate_card_id IN(SELECT rate_set_version_id
FROM okl_fe_rate_set_versions
WHERE std_rate_tmpl_ver_id = p_version_id);
SELECT effective_from_date
,effective_to_date
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_ver_number - 1;
SELECT max(effective_from_date)
FROM okl_fe_criteria_set ech
,okl_fe_criteria ecl
WHERE ecl.criteria_set_id = ech.criteria_set_id
AND ech.source_id = p_version_id AND source_object_code = 'SRT';
SELECT 'X'
FROM okl_fe_std_rt_tmp_vers
WHERE version_number = (SELECT MAX(version_number)
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id)
AND std_rate_tmpl_ver_id = p_srt_version_id;
SELECT 'X'
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_id = p_srt_id
AND std_rate_tmpl_ver_id <> p_srt_version_id
AND sts_code <> 'ABANDONED';
SELECT effective_from_date
,effective_to_date
,sts_code
,std_rate_tmpl_id
FROM okl_fe_std_rt_tmp_vers
WHERE std_rate_tmpl_ver_id = p_srt_version_id;
SELECT vers.rate_set_version_id id
,hdr.name name
,vers.version_number version_number
FROM okl_fe_rate_set_versions vers
,okl_ls_rt_fctr_sets_v hdr
WHERE vers.rate_set_id = hdr.id
AND vers.std_rate_tmpl_ver_id = p_version_id
AND vers.sts_code = 'ACTIVE';
l_update_header BOOLEAN;
l_update_version BOOLEAN;
l_update_header := false;
l_update_version := false;
l_update_version := true;
l_update_header := true;
l_update_version := true;
l_update_header := true;
l_update_version := true;
l_update_header := true;
IF l_update_version THEN
okl_srv_pvt.update_row(l_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,lp_srv_rec
,lx_srv_rec);
IF l_update_header THEN
okl_srt_pvt.update_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,lp_srtv_rec
,lx_srtv_rec);