[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c11(p_rate_matrix_id in number) is select distinct criteria_short_code
from pqh_rate_matrix_nodes a, ben_eligy_prfl_f b
where pl_id = p_rate_matrix_id
and criteria_short_code is not null
and a.eligy_prfl_id = b.eligy_prfl_id;
cursor c21 is select distinct information13 from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RBC_CRIT'; -- also consider 'DELETE' status rows
select information1 into l_rate_matrix_id from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'PLN';
FUNCTION allow_criteria_delete(p_eligy_criteria_id NUMBER) RETURN varchar2 IS
l_eligy_prfl_id number;
cursor c1 is select eligy_prfl_id from ben_eligy_crit_values_f
where eligy_criteria_id = p_eligy_criteria_id;
l_status := 'DeleteEnabled';
l_status := 'DeleteDisabled';
END allow_criteria_delete;
procedure delete_matrix_values(p_copy_entity_txn_id in number,
p_rate_matrix_node_id in number,
p_mode in varchar2
) is
begin
hr_utility.set_location('going for deleting matrix values row',100);
delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV';
update ben_copy_entity_results set dml_operation = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV';
delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV' and dml_operation = 'CREATE';
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV' and dml_operation in ('UPDATE','COPIED');
end delete_matrix_values;
procedure delete_matrix_rates(p_copy_entity_txn_id in number,
p_rate_matrix_node_id in number,
p_mode in varchar2
) is
begin
hr_utility.set_location('going for deleting matrix rates row',100);
delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR';
update ben_copy_entity_results set dml_operation = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR';
delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR' and dml_operation = 'CREATE';
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR' and dml_operation in ('UPDATE','COPIED');
end delete_matrix_rates;
procedure delete_matrix_nodes(p_copy_entity_txn_id in number,
p_pl_id in number,
p_level in number,
p_short_code in varchar2,
p_mode in varchar2
) is
l_rate_matrix_node_id number;
cursor c1 is select information1 from ben_copy_entity_results where
Copy_entity_txn_id = p_copy_entity_txn_id and
Information160 = p_level and
information13 = p_short_code and
Information261 = p_pl_id and
TABLE_ALIAS = 'RMN';
delete from ben_copy_entity_results where
Copy_entity_txn_id = p_copy_entity_txn_id and
Information160 = p_level and
information13 = p_short_code and
Information261 = p_pl_id and
TABLE_ALIAS = 'RBC_CRIT' and
dml_operation = 'CREATE';
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where
Copy_entity_txn_id = p_copy_entity_txn_id and
Information160 = p_level and
information13 = p_short_code and
Information261 = p_pl_id and
TABLE_ALIAS = 'RBC_CRIT' and
dml_operation in ('UPDATE','COPIED');
delete_matrix_values(p_copy_entity_txn_id,l_rate_matrix_node_id,p_mode);
delete_matrix_rates(p_copy_entity_txn_id,l_rate_matrix_node_id,p_mode);
delete from ben_copy_entity_results where
Copy_entity_txn_id = p_copy_entity_txn_id and
Information160 = p_level and
information13 = p_short_code and
Information261 = p_pl_id and
TABLE_ALIAS = 'RMN' and
dml_operation = 'CREATE';
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where
Copy_entity_txn_id = p_copy_entity_txn_id and
Information160 = p_level and
information13 = p_short_code and
Information261 = p_pl_id and
TABLE_ALIAS = 'RMN' and
dml_operation in ('UPDATE','COPIED');
end delete_matrix_nodes;
To check if there are criteria values present in rate matrix, check if there are any rows in ben_copy_entity_results with table_alias = 'RMN' and dml_operation <> 'DELETE' for the current copy_entity_txn_id.
To check if there are criteria attached to rate matrix, check if there are any rows in ben_copy_entity_results with table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE' for the current copy_entity_txn_id.
*/
function allow_hgrid_reorder(p_copy_entity_txn_id in number) return varchar2 IS
l_temp varchar2(1);
cursor c1 is select null from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMN' and dml_operation <> 'DELETE' and information160 <> 1;
cursor c2 is select null from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
select max(nvl(information160,1)) into l_max from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
select effective_start_date from ben_pl_f
where pl_id = p_pl_id
and pl_stat_cd = 'I'
and effective_start_date > p_effective_date
order by effective_start_date;
select transaction_category_id
into l_rbc_txn_cat
from pqh_transaction_categories
where short_name ='CRITERIA'
and business_group_id is null;
cursor c1 is select short_code from ben_eligy_criteria
where eligy_criteria_id = p_eligy_criteria_id_std
and business_group_id is null;
select eligy_criteria_id from ben_eligy_criteria
where short_code = p_short_code
and business_group_id = p_business_group_id;
cursor c1 is select * from ben_eligy_criteria
where eligy_criteria_id = p_eligy_criteria_id;
select BEN_ELIGY_CRITERIA_S.NEXTVAL into l_ben_eligy_criteria from dual;
SELECT flex_value_set_name
into l_flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = l_egl_rec.col1_value_set_id;
cursor c1 is select * from ben_eligy_criteria
where eligy_criteria_id = p_eligy_criteria_id;
SELECT flex_value_set_name
into l_flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = l_egl_rec.col1_value_set_id;
p_dml_operation => 'UPDATE',
p_information1 => l_egl_rec.eligy_Criteria_id,
p_information4 => l_egl_rec.business_group_id,
p_information5 => l_egl_rec.name,
p_information11 => l_egl_rec.short_code,
p_information12 => l_egl_rec.criteria_type,
p_information13 => l_egl_rec.crit_col1_val_type_cd,
p_information14 => l_egl_rec.crit_col1_datatype,
p_information15 => l_egl_rec.col1_lookup_type,
p_information16 => l_egl_rec.access_table_name1,
p_information17 => l_egl_rec.access_column_name1,
p_information18 => l_egl_rec.time_entry_access_table_name1,
p_information19 => l_egl_rec.time_entry_access_col_name1,
p_information20 => l_egl_rec.crit_col2_val_type_cd,
p_information21 => l_egl_rec.crit_col2_datatype,
p_information22 => l_egl_rec.col2_lookup_type,
p_information23 => l_egl_rec.access_table_name2,
p_information24 => l_egl_rec.access_column_name2,
p_information25 => l_egl_rec.time_entry_access_table_name2,
p_information26 => l_egl_rec.time_entry_access_col_name2,
p_information27 => l_egl_rec.allow_range_validation_flag,
p_information28 => l_egl_rec.user_defined_flag,
p_information29 => l_egl_rec.legislation_code,
p_information110 => l_egl_rec.egl_attribute_category,
p_information111 => l_egl_rec.egl_attribute1,
p_information112 => l_egl_rec.egl_attribute2,
p_information113 => l_egl_rec.egl_attribute3,
p_information114 => l_egl_rec.egl_attribute4,
p_information115 => l_egl_rec.egl_attribute5,
p_information116 => l_egl_rec.egl_attribute6,
p_information117 => l_egl_rec.egl_attribute7,
p_information118 => l_egl_rec.egl_attribute8,
p_information119 => l_egl_rec.egl_attribute9,
p_information120 => l_egl_rec.egl_attribute10,
p_information121 => l_egl_rec.egl_attribute11,
p_information122 => l_egl_rec.egl_attribute12,
p_information123 => l_egl_rec.egl_attribute13,
p_information124 => l_egl_rec.egl_attribute14,
p_information125 => l_egl_rec.egl_attribute15,
p_information126 => l_egl_rec.egl_attribute16,
p_information127 => l_egl_rec.egl_attribute17,
p_information128 => l_egl_rec.egl_attribute18,
p_information129 => l_egl_rec.egl_attribute19,
p_information130 => l_egl_rec.egl_attribute20,
p_information131 => l_egl_rec.egl_attribute21,
p_information132 => l_egl_rec.egl_attribute22,
p_information133 => l_egl_rec.egl_attribute23,
p_information134 => l_egl_rec.egl_attribute24,
p_information135 => l_egl_rec.egl_attribute25,
p_information136 => l_egl_rec.egl_attribute26,
p_information137 => l_egl_rec.egl_attribute27,
p_information138 => l_egl_rec.egl_attribute28,
p_information139 => l_egl_rec.egl_attribute29,
p_information140 => l_egl_rec.egl_attribute30,
p_information170 => l_egl_rec.name,
p_information185 => l_flex_value_set_name,
p_information219 => l_egl_rec.description,
p_information265 => l_egl_rec.object_version_number,
p_information266 => l_egl_rec.col1_value_set_id,
p_information267 => l_egl_rec.col2_value_set_id,
p_information268 => l_egl_rec.access_calc_rule,
p_information30 => l_egl_rec.allow_range_validation_flag2,
p_information269 => l_egl_rec.access_calc_rule2,
p_information270 => l_egl_rec.time_access_calc_rule1,
p_information271 => l_egl_rec.time_access_calc_rule2,
p_object_version_number => l_egl_ovn,
p_effective_date => p_effective_date);
l_last_updated_by ben_eligy_criteria.last_updated_by%type;
l_last_update_date ben_eligy_criteria.last_update_date%type;
l_last_update_login ben_eligy_criteria.last_update_login%type;
select flex_value_set_id
from fnd_flex_value_sets
where flex_value_set_name = p_valset_name;
select eligy_criteria_id
from ben_eligy_criteria
where short_code = p_short_code and business_group_id is null and criteria_type='STD';
select eligy_criteria_id
from ben_eligy_criteria
where short_code = p_short_code and business_group_id is not null and criteria_type='STD';
l_last_updated_by := fnd_load_util.owner_id(p_owner);
l_last_update_date := sysdate;
l_last_update_login := 0;
update ben_eligy_criteria set
name = p_name,
description = p_description,
crit_col1_val_type_cd = p_crit_col1_val_type_cd,
crit_col1_datatype = p_crit_col1_datatype,
col1_lookup_type = p_col1_lookup_type,
col1_value_set_id = l_col1_value_set_id,
access_table_name1 = p_access_table_name1 ,
access_column_name1 = p_access_column_name1,
crit_col2_val_type_cd = p_crit_col2_val_type_cd,
crit_col2_datatype = p_crit_col2_datatype,
col2_lookup_type = p_col2_lookup_type,
col2_value_set_id = l_col2_value_set_id,
access_table_name2 = p_access_table_name2,
access_column_name2 = p_access_column_name2,
allow_range_validation_flag = p_allow_range_validation_flag,
allow_range_validation_flag2 = p_allow_range_validation_flag2,
user_defined_flag = p_user_defined_flag,
business_group_id = to_number(p_business_group_id),
legislation_code = p_legislation_code,
criteria_type = 'STD',
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
where eligy_criteria_id = l_eligy_criteria_id;
update ben_eligy_criteria set
name = p_name,
description = p_description,
crit_col1_val_type_cd = p_crit_col1_val_type_cd,
crit_col1_datatype = p_crit_col1_datatype,
col1_lookup_type = p_col1_lookup_type,
col1_value_set_id = l_col1_value_set_id,
access_table_name1 = p_access_table_name1 ,
access_column_name1 = p_access_column_name1,
crit_col2_val_type_cd = p_crit_col2_val_type_cd,
crit_col2_datatype = p_crit_col2_datatype,
col2_lookup_type = p_col2_lookup_type,
col2_value_set_id = l_col2_value_set_id,
access_table_name2 = p_access_table_name2,
access_column_name2 = p_access_column_name2,
allow_range_validation_flag = p_allow_range_validation_flag,
allow_range_validation_flag2 = p_allow_range_validation_flag2,
user_defined_flag = p_user_defined_flag,
legislation_code = p_legislation_code,
criteria_type = 'STD',
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
where eligy_criteria_id = bg_crit_rec.eligy_criteria_id;
insert into ben_eligy_criteria
(
eligy_criteria_id,
short_code,
name,
description,
crit_col1_val_type_cd,
crit_col1_datatype,
col1_lookup_type,
col1_value_set_id,
access_table_name1,
access_column_name1,
crit_col2_val_type_cd,
crit_col2_datatype,
col2_lookup_type,
col2_value_set_id,
access_table_name2,
access_column_name2,
allow_range_validation_flag,
allow_range_validation_flag2,
user_defined_flag,
business_group_id,
legislation_code,
criteria_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
values
(
ben_eligy_criteria_s.nextval,
p_short_code,
p_name,
p_description,
p_crit_col1_val_type_cd,
p_crit_col1_datatype,
p_col1_lookup_type,
l_col1_value_set_id,
p_access_table_name1,
p_access_column_name1,
p_crit_col2_val_type_cd,
p_crit_col2_datatype,
p_col2_lookup_type,
l_col2_value_set_id,
p_access_table_name2,
p_access_column_name2,
p_allow_range_validation_flag,
p_allow_range_validation_flag2,
p_user_defined_flag,
null,
p_legislation_code,
'STD',
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_ovn
);
procedure create_update_criteria(p_mode in varchar2,
p_eligy_criteria_id in number,
p_business_area in varchar2,
p_business_group_id in number,
p_effective_date in date,
p_criteria_type in varchar2,
p_copy_entity_txn_id in out nocopy number,
p_copy_entity_result_id out nocopy number,
p_copy_entity_result_ovn out nocopy number) is
l_cet_id number;
elsif p_mode ='UPDATE' and p_copy_entity_txn_id is null then
-- create the copy entity txn row
-- copy the EGL data into staging area and set dml_operation to 'UPDATE'
hr_utility.set_location('creating cet row for update',10);
end create_update_criteria;
cursor c1 is select * from ben_copy_entity_results
where table_alias = 'EGL'
and copy_entity_txn_id = p_copy_entity_txn_id;
if r_egl.dml_operation ='UPDATE' then
hr_utility.set_location('going for update operation ',10);
ben_eligy_criteria_api.update_eligy_criteria(
p_validate => FALSE,
p_eligy_criteria_id => l_eligy_criteria_id,
p_name => r_egl.information170,
p_short_code => r_egl.information11,
p_description => r_egl.information219,
p_criteria_type => r_egl.information12,
p_crit_col1_val_type_cd => r_egl.information13,
p_crit_col1_datatype => r_egl.information14,
p_col1_lookup_type => r_egl.information15,
p_col1_value_set_id => r_egl.information266,
p_access_table_name1 => r_egl.information16,
p_access_column_name1 => r_egl.information17,
p_time_entry_access_tab_nam1 => r_egl.information18,
p_time_entry_access_col_nam1 => r_egl.information19,
p_crit_col2_val_type_cd => r_egl.information20,
p_crit_col2_datatype => r_egl.information21,
p_col2_lookup_type => r_egl.information22,
p_col2_value_set_id => r_egl.information267,
p_access_table_name2 => r_egl.information23,
p_access_column_name2 => r_egl.information24,
p_time_entry_access_tab_nam2 => r_egl.information25,
p_time_entry_access_col_nam2 => r_egl.information26,
p_access_calc_rule => r_egl.information268,
p_allow_range_validation_flg => r_egl.information27,
p_user_defined_flag => r_egl.information28,
p_business_group_id => r_egl.information4,
p_legislation_code => r_egl.information29,
p_egl_attribute_category => r_egl.information110,
p_egl_attribute1 => r_egl.information111,
p_egl_attribute2 => r_egl.information112,
p_egl_attribute3 => r_egl.information113,
p_egl_attribute4 => r_egl.information114,
p_egl_attribute5 => r_egl.information115,
p_egl_attribute6 => r_egl.information116,
p_egl_attribute7 => r_egl.information117,
p_egl_attribute8 => r_egl.information118,
p_egl_attribute9 => r_egl.information119,
p_egl_attribute10 => r_egl.information120,
p_egl_attribute11 => r_egl.information121,
p_egl_attribute12 => r_egl.information122,
p_egl_attribute13 => r_egl.information123,
p_egl_attribute14 => r_egl.information124,
p_egl_attribute15 => r_egl.information125,
p_egl_attribute16 => r_egl.information126,
p_egl_attribute17 => r_egl.information127,
p_egl_attribute18 => r_egl.information128,
p_egl_attribute19 => r_egl.information129,
p_egl_attribute20 => r_egl.information130,
p_egl_attribute21 => r_egl.information131,
p_egl_attribute22 => r_egl.information132,
p_egl_attribute23 => r_egl.information133,
p_egl_attribute24 => r_egl.information134,
p_egl_attribute25 => r_egl.information135,
p_egl_attribute26 => r_egl.information136,
p_egl_attribute27 => r_egl.information137,
p_egl_attribute28 => r_egl.information138,
p_egl_attribute29 => r_egl.information139,
p_egl_attribute30 => r_egl.information140,
p_object_version_number => l_eligy_ovn,
p_effective_date => l_effective_date,
p_allow_range_validation_flag2 => r_egl.information30,
p_access_calc_rule2 => r_egl.information269,
p_time_access_calc_rule1 => r_egl.information270,
p_time_access_calc_rule2 => r_egl.information271
);
cursor c1 is select rate_matrix_rate_id from pqh_rate_matrix_rates_f
where criteria_rate_defn_id = p_criteria_rate_defn_id;
cursor c2 is select information1 from ben_copy_entity_results where
information1 = p_criteria_rate_defn_id and table_alias = 'RCR';
procedure insert_rate_defn_tl(rateid in number,
ratename in varchar2,
lang in varchar2,
slang in varchar2,
cdate in date,
cperson in number) is
begin
insert into pqh_criteria_rate_defn_tl(CRITERIA_RATE_DEFN_ID,NAME,LANGUAGE,
SOURCE_LANG,CREATION_DATE,CREATED_BY)
values(rateid,ratename,lang,slang,cdate,cperson);
end insert_rate_defn_tl;
cursor c1 is select rate_factor_on_elmnt_id from
Pqh_rate_factor_on_elmnts rf, Pqh_criteria_rate_elements re
where rf.criteria_rate_element_id = re.criteria_rate_element_id
and re.criteria_rate_defn_id = critId
and rf.criteria_rate_factor_id = parentId;
delete from Pqh_rate_factor_on_elmnts
where rate_factor_on_elmnt_id = l_rate_factor_id;
FUNCTION is_used_in_matrix(p_selected_rate_matrix NUMBER, p_criteria_rate_defn_id NUMBER) RETURN varchar2 IS
l_return_vlaue varchar2(1);
Select 'Y' INTO l_return_vlaue from
pqh_rate_matrix_rates_f t1, pqh_rate_matrix_nodes t2
Where t2.pl_id = p_selected_rate_matrix
and t2.rate_matrix_node_id = t1.rate_matrix_node_id
and sysdate between t1.effective_start_date and t1.effective_end_date
and t1.criteria_rate_defn_id = p_criteria_rate_defn_id group by t1.criteria_rate_defn_id;
SELECT SHORT_NAME FROM pqh_criteria_rate_defn_vl
where upper(short_name) = upper(sname)
and CRITERIA_RATE_DEFN_ID <> rateId
and business_group_id = bgId;
SELECT NAME FROM pqh_criteria_rate_defn_vl
where upper(name) = upper(cname)
and CRITERIA_RATE_DEFN_ID <> rateId
and business_group_id = bgId;
delete from Pqh_criteria_rate_factors
where criteria_rate_defn_id = rateTypeId;
Select 'x'
from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'RMR'
AND information162 = p_crd_id
and dml_operation <> 'DELETE'
and information1 is not null;
Select information1
from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PLN';
Select copy_entity_result_id, information1 , nvl(information5,'name') crd_name,information160
from ben_copy_entity_results
Where table_alias = 'RCR'
and copy_entity_txn_id = p_copy_entity_txn_id
and (information1 = p_criteria_rate_defn_id OR
information1 in (select criteria_rate_defn_id
from pqh_criteria_rate_factors
Where parent_criteria_rate_defn_id = p_criteria_rate_defn_id
and (parent_rate_matrix_id is null or parent_rate_matrix_id = p_rate_matrix_id)
)
);
Delete from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'RMR'
AND information162 = l_criteria_rate_defn_id
and dml_operation <> 'DELETE';
Update ben_copy_entity_results
set dml_operation = 'DELETE'
Where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'RMR'
AND information162 = l_criteria_rate_defn_id
and dml_operation <> 'DELETE';
Delete from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RCR'
and copy_entity_result_id = l_copy_entity_result_id;
Update ben_copy_entity_results
set information160 = (information160 - 1)
Where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RCR'
and information160 > l_curr_order_number;
Select copy_entity_result_id, information1 , nvl(information5,'name') crd_name,information160
from ben_copy_entity_results
Where table_alias = 'RCR'
and copy_entity_txn_id = p_copy_entity_txn_id
order by information160;
Select information1
from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PLN';
select a.parent_criteria_rate_defn_id parent_id, b.name parent_name
from pqh_criteria_rate_factors a, pqh_criteria_rate_defn_vl b
Where a.criteria_rate_defn_id = p_criteria_rate_defn_id
and (parent_rate_matrix_id is null or parent_rate_matrix_id = p_rate_matrix_id)
and a.parent_criteria_rate_defn_id = b.criteria_rate_defn_id;
Select 'x'
from ben_copy_entity_results
Where table_alias = 'RCR'
and copy_entity_txn_id = p_copy_entity_txn_id
and information1 = p_parent;
Update ben_copy_entity_results
set information160 = l_new_order_number
Where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RCR'
and copy_entity_result_id = l_copy_entity_result_id;
Select max(information160) + 1
from ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RCR';
select max(nvl(information160,1))into l_max from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in('RBC_CRIT', 'PLN') and dml_operation <> 'DELETE';
procedure delete_rate_values(p_copy_entity_txn_id in number,
p_copy_entity_result_id in number
) is
l_copy_entity_result_id number;
cursor c1 is select copy_entity_result_id, information160
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and gs_parent_entity_result_id = p_copy_entity_result_id
and table_alias in ('RMN','RMV');
cursor c2 is select information1,copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and (copy_entity_result_id = l_copy_entity_result_id
or gs_parent_entity_result_id = l_copy_entity_result_id)
and table_alias in ('RMN','RMV');
delete_rate_values(p_copy_entity_txn_id, l_copy_entity_result_id);
delete from ben_copy_entity_results
where INFORMATION1 = l_copy_entity_result_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RBR';
delete from ben_copy_entity_results
where INFORMATION1 = l_rate_matrix_node_id
and copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id=l_copy_entity_result_id
and table_alias in ('RMV','RMN')
and dml_operation = 'CREATE';
update ben_copy_entity_results
set dml_operation = 'DELETE',datetrack_mode = 'DELETE'
where INFORMATION1 = l_rate_matrix_node_id
and copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id=l_copy_entity_result_id
and table_alias in ('RMV','RMN')
and dml_operation in ('UPDATE','COPIED');
delete from ben_copy_entity_results
where INFORMATION1 = p_copy_entity_result_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RBR';
delete from ben_copy_entity_results
where copy_entity_result_id = p_copy_entity_result_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('RMV','RMN')
and dml_operation = 'CREATE';
update ben_copy_entity_results
set dml_operation = 'DELETE',datetrack_mode = 'DELETE'
where copy_entity_result_id = p_copy_entity_result_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('RMV','RMN')
and dml_operation in ('UPDATE','COPIED');
select max(information160) into l_max_level_number from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
cursor c1 is select copy_entity_result_id from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'PLN';
delete from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id;
delete from pqh_copy_entity_txns where copy_entity_txn_id = p_copy_entity_txn_id;
select DEFINE_MAX_RATE_FLAG, DEFINE_MIN_RATE_FLAG, DEFINE_MID_RATE_FLAG, DEFINE_STD_RATE_FLAG
into l_max_flag, l_min_flag, l_mid_flag, l_dfl_flag
from pqh_criteria_rate_defn
where CRITERIA_RATE_DEFN_ID = critId;
cursor c1 is select name from fnd_currencies_vl
where currency_code = p_currency_code;
cursor c1 is select formula_name from ff_formulas_f
where formula_id = p_formula_id;
Select validation_type, format_type
from fnd_flex_value_sets
Where flex_value_set_id = p_value_set_id;
select nvl(id_column_type,'O')
from fnd_flex_validation_tables
where flex_value_set_id = p_value_set_id;