[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT crit_cat_name
,ecc_ac_flag
FROM okl_fe_crit_cat_def_v
WHERE crit_cat_def_id = p_crit_cat_def_id;
This procedure deletes the Eligibility criteria attached to a particular
source object identified by p_source_id and p_source_type.
**/
PROCEDURE delete_eligibility_criteria(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_source_id IN number
,p_source_type IN varchar2) IS
l_source_id number;
SELECT criteria_set_id
FROM okl_fe_criteria_set
WHERE source_id = p_source_id AND source_object_code = p_source_type;
SELECT criteria_id
FROM okl_fe_criteria
WHERE criteria_set_id = p_criteria_set_id;
SELECT criterion_value_id
FROM okl_fe_criterion_values
WHERE criteria_id = p_criteria_id;
l_api_name CONSTANT varchar2(30) := 'DELETE_ELIG_CRITERIA';
l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_ec_values_pvt.delete_eligibility_criteria';
,'begin debug OKLRECCB.pls call delete_eligibility_criteria');
okl_ech_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_ech_rec => lp_ech_rec);
,'Procedure okl_ech_pvt.delete_row returned with status ' ||
l_return_status);
okl_ecl_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_ecl_tbl => lp_ecl_tbl);
,'Procedure okl_ecl_pvt.delete_row returned with status ' ||
l_return_status);
okl_ecv_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_ecv_tbl => lp_ecv_tbl);
,'Procedure okl_ecv_pvt.delete_row returned with status ' ||
l_return_status);
,'end debug OKLRECVB.pls.pls call delete_eligibility_criteria');
END delete_eligibility_criteria;
This procedure do insert/update of eligibility criteria passed to it as
parameters. The is_new_flag='Y' in p_ecl_tbl(i) signifies insert of Criteria.
p_ech_rec.criteria_Set_id = null signifies insert of Criteria set record.
p_ecv_tbl(i).criterion_value_id signifies insert of criterion value record.
p_ecv_tbl(i).criteria_id should be pointing to p_ecl_tbl(i).criteria_id,
to identify the criterion value rows corressponding to a particular criterion
line.
**/
PROCEDURE handle_eligibility_criteria(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_ech_rec IN okl_ech_rec
,x_ech_rec OUT NOCOPY okl_ech_rec
,p_ecl_tbl IN okl_ecl_tbl
,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
,p_ecv_tbl IN okl_ecv_tbl
,x_ecv_tbl OUT NOCOPY okl_ecv_tbl
,p_source_eff_from IN date
,p_source_eff_to IN date) IS
CURSOR l_data_type_csr(p_crit_cat_def_id IN number) IS
SELECT data_type_code
,value_type_code
,source_yn
FROM okl_fe_crit_cat_def_b
WHERE crit_cat_def_id = p_crit_cat_def_id;
okl_ech_pvt.insert_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ech_rec
,lx_ech_rec);
,'Procedure okl_ech_pvt.insert_row returned with status ' ||
l_return_status);
okl_ech_pvt.update_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ech_rec
,lx_ech_rec);
,'Procedure okl_ech_pvt.update_row returned with status ' ||
l_return_status);
okl_ecl_pvt.insert_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecl_tbl(i)
,lx_ecl_tbl(i));
,'Procedure okl_ecl_pvt.insert_row returned with status ' ||
l_return_status);
okl_ecv_pvt.insert_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecv_tbl(j)
,lx_ecv_tbl(j));
,'Procedure okl_ecv_pvt.insert_row returned with status ' ||
l_return_status);
okl_ecv_pvt.update_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecv_tbl(j)
,lx_ecv_tbl(j));
,'Procedure okl_ecv_pvt.update_row returned with status ' ||
l_return_status);
okl_ecl_pvt.update_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecl_tbl(i)
,lx_ecl_tbl(i));
,'Procedure okl_ecl_pvt.update_row returned with status ' ||
l_return_status);
okl_ecv_pvt.insert_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecv_tbl(j)
,lx_ecv_tbl(j));
,'Procedure okl_ecv_pvt.insert_row returned with status ' ||
l_return_status);
okl_ecv_pvt.update_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ecv_tbl(j)
,lx_ecv_tbl(j));
,'Procedure okl_ecv_pvt.update_row returned with status ' ||
l_return_status);
okl_ech_pvt.delete_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,lp_ech_rec);
,'Procedure okl_ech_pvt.delete_row returned with status ' ||
l_return_status);
SELECT criterion_value_id
FROM okl_fe_criterion_values
WHERE okl_fe_criterion_values.criteria_id = p_line_id;
okl_ecv_pvt.delete_row(p_api_version
,okl_api.g_false
,l_return_status
,x_msg_count
,x_msg_data
,l_ecv_rec);
okl_ecl_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_ecl_rec => lp_ecl_rec);
SELECT criteria_set_id
,object_version_number
,match_criteria_code
,validation_code
FROM okl_fe_criteria_set
WHERE source_id = p_source_id AND source_object_code = p_source_type;
SELECT criteria_id
,object_version_number
,criteria_set_id
,crit_cat_def_id
,match_criteria_code
,effective_from_date
,effective_to_date
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
FROM okl_fe_criterion_values
WHERE criteria_id = p_criteria_id;
SELECT criteria_set_id
,object_version_number
,match_criteria_code
,validation_code
FROM okl_fe_criteria_set
WHERE source_id = p_source_id AND source_object_code = p_source_type;
SELECT criteria_id
,object_version_number
,criteria_set_id
,crit_cat_def_id
,match_criteria_code
,effective_from_date
,effective_to_date
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
FROM okl_fe_criterion_values
WHERE criteria_id = p_criteria_id;
SELECT ecl.criteria_id
,ecl.criteria_set_id
,ecl.crit_cat_def_id
,ecl.match_criteria_code
,ecl.effective_from_date
,ecl.effective_to_date
,ecl.object_version_number
FROM okl_fe_criteria ecl
,okl_fe_criteria_set ech
WHERE ech.source_id = p_source_id
AND ech.source_object_code = p_source_type
AND ecl.criteria_set_id = ech.criteria_set_id;
okl_ecl_pvt.update_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_ecl_tbl
,lx_ecl_tbl);
,'Procedure okl_ecl_pvt.update_row returned with status ' ||
l_return_status);