[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select pet.element_type_id,
pet.element_name,
pec.classification_name,
pet.business_group_id business_group_id,
pet.legislation_code,
pet.indirect_only_flag,
min(pet.effective_start_date) effective_date
From
pay_element_types_f pet,
pay_element_classifications pec,
per_business_groups pbg
Where
pet.element_type_id = c_element_type_id
and pet.classification_id = pec.classification_id
and nvl(pet.business_group_id,pbg.business_group_id) = pbg.business_group_id
and nvl(pet.legislation_code,pbg.legislation_code) = 'FR'
Group By
pet.element_type_id,
pet.element_name,
pec.classification_name,
pet.business_group_id,
pet.legislation_code,
pet.indirect_only_flag;
select pur.row_low_range_or_name row_id,puci.value
from pay_user_tables put
, pay_user_rows_f pur
, pay_user_columns puc
, pay_user_column_instances_f puci
, pay_user_columns puc_rule_type
, pay_user_column_instances_f puci_rule_type
where put.user_table_name = 'FR_RUN_TYPE_RULES'
and put.legislation_code = 'FR'
and put.business_group_id is null
and puc.user_table_id = put.user_table_id
and puc.user_column_name = 'ID'
and pur.user_table_id = put.user_table_id
and csr_effective_date
between pur.effective_start_date and pur.effective_end_date
and puci.user_column_id = puc.user_column_id
and puci.user_row_id = pur.user_row_id
and puci.value in (c_element_name , c_classification_name)
and csr_effective_date
between puci.effective_start_date and puci.effective_end_date
and puc_rule_type.user_table_id = put.user_table_id
and puc_rule_type.user_column_name = 'Rule Type'
and puci_rule_type.user_column_id = puc_rule_type.user_column_id
and puci_rule_type.user_row_id = pur.user_row_id
and csr_effective_date
between puci_rule_type.effective_start_date and puci_rule_type.effective_end_date
order by puci_rule_type.value;
Select etu.element_type_usage_id
,etu.effective_start_date effective_date
,etu.object_version_number
,etu.business_group_id
,etu.legislation_code
From pay_element_type_usages_f etu
,pay_run_types_f rt
Where rt.legislation_code = 'FR'
And rt.shortname = c_run_type
And etu.run_type_id = rt.run_type_id
And etu.element_type_id = p_element_type_id;
Select run_type_id
From pay_run_types_f
Where shortname = p_run_type
and legislation_code = p_legislation_code;
Select puci.value
From pay_user_tables put
,pay_user_rows_f pur
,pay_user_columns puc
,pay_user_column_instances_f puci
Where put.user_table_name = p_table_name
and put.legislation_code = 'FR'
and put.business_group_id Is Null
and put.user_table_id = pur.user_table_id
and pur.row_low_range_or_name = p_row_value
and ((pur.legislation_code = 'FR'and pur.business_group_id Is Null)
or
(pur.legislation_code Is Null and pur.business_group_id =p_bus_group_id))
and p_effective_date Between pur.effective_start_date And pur.effective_end_date
and puc.user_table_id = put.user_table_id
and puc.user_column_name = p_col_name
and puc.legislation_code = 'FR'
and puc.business_group_id Is Null
and puci.user_row_id = pur.user_row_id
and puci.user_column_id = puc.user_column_id
and p_effective_date Between puci.effective_start_date And puci.effective_end_date;
pay_element_type_usage_api.delete_element_type_usage( p_validate =>null
,p_effective_date =>l_run_type_usage_rec.effective_date
,p_datetrack_delete_mode =>'ZAP'
,p_element_type_usage_id =>l_run_type_usage_rec.element_type_usage_id
,p_object_version_number =>l_run_type_usage_rec.object_version_number
,p_business_group_id =>l_run_type_usage_rec.business_group_id
,p_legislation_code =>l_run_type_usage_rec.legislation_code
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
);
Select etu.element_type_usage_id
,etu.effective_start_date effective_date
,etu.object_version_number
,etu.business_group_id
,etu.legislation_code
From pay_element_type_usages_f etu
,pay_run_types_f rt
Where rt.legislation_code = 'FR'
And rt.shortname In ('STANDARD', 'NET', 'SICKNESS')
And etu.run_type_id = rt.run_type_id;
Select pet.element_type_id
,pet.element_name
,pet.business_group_id
,pet.legislation_code
,Min(pet.effective_start_date) effective_date
From pay_element_types_f pet
,per_business_groups pbg
Where Nvl(pet.indirect_only_flag, 'N') = 'N'
And pbg.business_group_id = pet.business_group_id
And pbg.legislation_code = 'FR'
And pet.legislation_code Is Null
Group By pet.element_type_id,
pet.element_name,
pet.business_group_id,
pet.legislation_code
union all
Select pet.element_type_id
,pet.element_name
,pet.business_group_id
,pet.legislation_code
,Min(pet.effective_start_date) effective_date
From pay_element_types_f pet
Where Nvl(pet.indirect_only_flag, 'N') = 'N'
And pet.legislation_code = 'FR'
And pet.business_group_id Is Null
Group By pet.element_type_id,
pet.element_name,
pet.business_group_id,
pet.legislation_code ;
--1.Delete all existing usages
For l_run_type_usage_rec In csr_run_type_usage_all Loop
hr_utility.set_location('Deleting run type usage '||l_proc,10);
pay_element_type_usage_api.delete_element_type_usage( p_validate =>null
,p_effective_date =>l_run_type_usage_rec.effective_date
,p_datetrack_delete_mode =>'ZAP'
,p_element_type_usage_id =>l_run_type_usage_rec.element_type_usage_id
,p_object_version_number =>l_run_type_usage_rec.object_version_number
,p_business_group_id =>l_run_type_usage_rec.business_group_id
,p_legislation_code =>l_run_type_usage_rec.legislation_code
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
);