The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure inserts/updates the lease rate factors and levels.
If p_lrfv_tbl(i).IS_NEW_FLAG = 'Y' then record is inserted else updated.
If p_lrlv_tbl(i).rate_Set_level_id=null then record is inserted else updated.
p_lrlv_tbl(i).(i).rate_set_factor_id should be pointing to appropriate record in
p_lrfv_tbl, to identify the levels corressponding to factor.
**/
PROCEDURE handle_lrf_ents(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_lrfv_tbl IN lrfv_tbl_type
,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
,p_lrlv_tbl IN okl_lrlv_tbl
,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
lp_lrfv_tbl lrfv_tbl_type;
okl_lrf_pvt.insert_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrfv_tbl(i)
,lx_lrfv_tbl(i));
,'Procedure okl_lrf_pvt.insert_row with status ' ||
l_return_status);
okl_lrl_pvt.insert_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrlv_tbl(j)
,lx_lrlv_tbl(j));
,'Procedure okl_lrl_pvt.insert_row with status ' ||
l_return_status);
okl_lrl_pvt.update_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrlv_tbl(j)
,lx_lrlv_tbl(j));
,'Procedure okl_lrl_pvt.update_row with status ' ||
l_return_status);
okl_lrf_pvt.update_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrfv_tbl(i)
,lx_lrfv_tbl(i));
,'Procedure okl_lrf_pvt.update_row with status ' ||
l_return_status);
IF (lp_lrlv_tbl(j).rate_set_factor_id = lp_lrfv_tbl(i).id) THEN --populate the ecl_id with the id of the recently inserted record in okl_ec_lines tablein the
lp_lrlv_tbl(j).rate_set_factor_id := lx_lrfv_tbl(i).id;
okl_lrl_pvt.insert_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrlv_tbl(j)
,lx_lrlv_tbl(j));
,'Procedure okl_lrl_pvt.insert_row with status ' ||
l_return_status);
okl_lrl_pvt.update_row(l_api_version
,g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_lrlv_tbl(j)
,lx_lrlv_tbl(j));
,'Procedure okl_lrl_pvt.update_row with status ' ||
l_return_status);
This procedure deletes all the lease rate factors and corressponding levels
for rate set version id p_lrv_id.
**/
PROCEDURE delete_lease_rate_factors(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_lrv_id IN number) IS
l_lrv_id number;
SELECT id
FROM okl_ls_rt_fctr_ents
WHERE rate_set_version_id = p_lrv_id;
SELECT rate_set_level_id
FROM okl_fe_rate_set_levels
WHERE rate_set_version_id = p_lrv_id;
l_api_name CONSTANT varchar2(30) := 'delete_lrf';
l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.delete_lease_rate_factors';
,'begin debug OKLPLRFB.pls call delete_lease_rate_factors');
okl_lrl_pvt.delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrlv_tbl => l_lrlv_tbl);
,'Procedure OKL_LRL_PVT.delete_row returned with status ' ||
l_return_status);
okl_lrf_pvt.delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrfv_tbl => l_lrfv_tbl);
,'Procedure OKL_LRF_PVT.delete_row returned with status ' ||
l_return_status);
,'end debug OKLRECVB.pls.pls call delete_lease_rate_factors');
END delete_lease_rate_factors;
This procedure deletes the lease rate factor row and its corressponding
levels.
**/
PROCEDURE remove_lrs_factor (p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_lrfv_rec IN lrfv_rec_type) IS
l_lrfv_rec lrfv_rec_type;
SELECT rate_set_level_id
FROM okl_fe_rate_set_levels
WHERE rate_set_factor_id = p_lrfv_rec.id;
okl_lrl_pvt.delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrlv_tbl => l_lrlv_tbl);
END IF; --delete factor row
okl_lrf_pvt.delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrfv_rec => l_lrfv_rec);
This procedure deletes the lease rate factor level.
**/
PROCEDURE remove_lrs_level(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_lrlv_rec IN okl_lrlv_rec) IS
l_lrlv_rec okl_lrlv_rec;
okl_lrl_pvt.delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrlv_rec => l_lrlv_rec);
SELECT decode(p_freq, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)
INTO l_freq
FROM dual;
SELECT a.rate_type_code
,(b.srt_rate+nvl(b.spread,0)) srt_rate
,min_adj_rate
,max_adj_rate
FROM okl_fe_std_rt_tmp_v a
,okl_fe_std_rt_tmp_vers b
WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id
AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id;
SELECT (c.value+nvl(b.spread,0)) value
FROM okl_fe_std_rt_tmp_v a
,okl_fe_std_rt_tmp_vers b
,okl_index_values c
WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id AND a.index_id = c.idx_id
AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id
AND lrs_eff_from BETWEEN c.datetime_valid AND nvl(c.datetime_invalid, lrs_eff_from + 1);
This procedure deletes the existing lease rate factors for a lease rate set
version.
**/
PROCEDURE handle_lease_rate_factors(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_lrtv_rec IN lrtv_rec_type
,p_lrvv_rec IN okl_lrvv_rec
,p_lrfv_tbl IN lrfv_tbl_type
,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
,p_lrlv_tbl IN okl_lrlv_tbl
,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
lp_lrvv_rec okl_lrvv_rec;
delete_lease_rate_factors(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrv_id => lp_lrvv_rec.rate_set_version_id);
,'Procedure delete_lease_rate_factors with status ' ||
l_return_status);
This procedure is a wrapper to insert/update the lease rate factors and
submit the lease rate set for approval.
**/
PROCEDURE handle_lrf_submit(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_lrtv_rec IN lrtv_rec_type
,p_lrvv_rec IN okl_lrvv_rec
,p_lrfv_tbl IN lrfv_tbl_type
,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
,p_lrlv_tbl IN okl_lrlv_tbl
,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
lp_lrvv_rec okl_lrvv_rec;
delete_lease_rate_factors(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lrv_id => lp_lrvv_rec.rate_set_version_id);