[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
fra.formula_id,
fra.formula_name,
fty.formula_type_id,
fty.formula_type_name
from ff_formulas_f fra,
ff_formula_types fty,
pay_status_processing_rules_f spr
where fty.formula_type_id = fra.formula_type_id
and fra.formula_id = spr.formula_id
and spr.assignment_status_type_id is null
and spr.element_type_id = c_element_type_id
and p_effective_start_date between fra.effective_start_date
and fra.effective_end_date
and p_effective_start_date between spr.effective_start_date
and spr.effective_end_date;
select element_type_id
from pay_element_types_f
where element_name = c_object_name
and business_group_id = p_business_group_id;
select ptco.core_object_id
from pay_shadow_balance_types psbt,
pay_template_core_objects ptco
where psbt.template_id = p_template_id
and psbt.balance_name = c_object_name
and ptco.template_id = psbt.template_id
and ptco.shadow_object_id = psbt.balance_type_id;
select formula_id
from pay_shadow_formulas
where formula_name = p_formula_name
and business_group_id = p_business_group_id
and template_type = 'U';
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name=upper(c_name)
AND business_group_id=p_business_group_id
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = c_name
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = c_element_type_id
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date
AND name = c_name;
SELECT status_processing_rule_id
FROM pay_status_processing_rules_f
WHERE element_type_id = c_element_type_id
AND formula_id = c_formula_id
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT pay_formula_result_rules_s.nextval
INTO l_formula_result_rule_id
FROM dual;
pay_formula_result_rules_pkg.insert_row
(p_rowid => l_rowid
,p_formula_result_rule_id => l_formula_result_rule_id
,p_effective_start_date => trunc(p_scheme_start_date)
,p_effective_end_date => trunc(p_scheme_end_date)
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_element_type_id => l_bik_er_ele_id
,p_status_processing_rule_id => l_er_status_proc_rule_id
,p_result_name => 'DEDUCTION_AMT'
,p_result_rule_type => 'I'
,p_legislation_subgroup => NULL
,p_severity_level => NULL
,p_input_value_id => l_bik_er_iv
,p_session_date => p_scheme_start_date
,p_created_by => -1
);
SELECT pay_formula_result_rules_s.nextval
INTO l_formula_result_rule_id
FROM dual;
pay_formula_result_rules_pkg.insert_row
(p_rowid => l_rowid
,p_formula_result_rule_id => l_formula_result_rule_id
,p_effective_start_date => trunc(p_scheme_start_date)
,p_effective_end_date => trunc(p_scheme_end_date)
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_element_type_id => l_bik_er_ele_id
,p_status_processing_rule_id => l_er_status_proc_rule_id
,p_result_name => 'DEDUCTION_AMT'
,p_result_rule_type => 'I'
,p_legislation_subgroup => NULL
,p_severity_level => NULL
,p_input_value_id => l_bik_er_iv
,p_session_date => p_scheme_start_date
,p_created_by => -1
);
procedure Update_Ipval_Defval(p_ele_name in Varchar2
,p_ip_name in Varchar2
,p_def_value in Varchar2
,p_business_group_id IN Number
)
is
cursor csr_getinput(c_ele_name varchar2
,c_iv_name varchar2) is
select input_value_id
,piv.name
,piv.element_type_id
from pay_input_values_f piv
,pay_element_types_f pet
where element_name = c_ele_name
and piv.element_type_id = pet.element_type_id
and (piv.business_group_id = p_business_group_id or
piv.business_group_id is null)
and piv.name = c_iv_name
and (piv.legislation_code = 'IE' or
piv.legislation_code is null);
select rowid
from pay_input_values_f
where input_value_id = c_ip_id
and element_type_id = c_element_type_id
for update nowait;
'update_ipval_defval';
update pay_input_values_f
set default_value = p_def_value
where rowid = csr_updinput_rec.rowid;
end Update_Ipval_Defval;
select pension_category
from pqp_pension_types_f
where pension_type_id = c_pen_type_id
and c_effective_date between effective_start_date and effective_end_date;
select balance_type_id
from pay_balance_types
where balance_name = c_bal_name
and legislation_code='IE'
and business_group_id is null;
select element_type_id
,object_version_number
from pay_shadow_element_types
where template_id = l_template_id
and element_name = c_ele_name;
select balance_type_id
,object_version_number
from pay_shadow_balance_types
where template_id = l_template_id
and balance_name = c_bal_name;
select element_type_id, object_version_number, relative_processing_priority, third_party_pay_only_flag, classification_name
from pay_shadow_element_types
where template_id = l_template_id
and element_name = c_shd_elename;
select input_value_id
from pay_input_values_f
where element_type_id = c_ele_typeid
and business_group_id = p_business_group_id
and name = 'Pay Value'
and c_effective_date between effective_start_date
and effective_end_date;
select input_value_id
from pay_input_values_f
where element_type_id = c_ele_typeid
and business_group_id = p_business_group_id
and name = c_ipv_name
and c_effective_date between effective_start_date
and effective_end_date;
select ee_contribution_bal_type_id
from pqp_pension_types_f
where pension_type_id = c_pension_type_id
and business_group_id = p_business_group_id
and c_effective_date between effective_start_date
and effective_end_date;
select *
from pqp_pension_types_f
where pension_type_id = c_pension_type_id
and business_group_id = p_business_group_id
and c_effective_date between effective_start_date
and effective_end_date;
select er_contribution_bal_type_id
from pqp_pension_types_f
where pension_type_id = c_pension_type_id
and business_group_id = p_business_group_id
and c_effective_date between effective_start_date
and effective_end_date;
select template_id
from pay_element_templates
where template_name = l_template_name
and legislation_code = p_legislation_code
and template_type = 'T'
and business_group_id is null;
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_effective_start_date
,p_element_type_id => l_shadow_element(i).element_type_id
,p_description => l_shadow_element(i).description
,p_reporting_name => l_shadow_element(i).reporting_name
,p_post_termination_rule => p_termination_rule
,p_standard_link_flag => l_std_link_flag
,p_relative_processing_priority => l_shadow_element(i).priority
,p_object_version_number => l_shadow_element(i).object_version_number
,p_third_party_pay_only_flag => l_shadow_element(i).third_party_pay_only_flag
,p_classification_name => l_shadow_element(i).classification_name
);
hr_utility.set_location('..Updated Shadow element', 25);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Pension Category'
,l_pension_category
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'PRSA2 Certificate'
,p_prsa2_certificate
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Pension Type ID'
,p_pension_type_id
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' EE Taxable Contribution'
,'Contribution Amount'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' EE Taxable Contribution'
,'Excess Contribution Amount'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Setup'
,'EE Pension Deduction'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' ER Contribution'
,'Pension Type ID'
,p_pension_type_id
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Setup'
,'ER Contribution'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Setup'
,'EE Taxable Contribution'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Setup'
,'EE Arrears'
,fnd_number.number_to_canonical(0)
,p_business_group_id);
Pay_Balance_Feeds_f_pkg.Insert_Row(
X_Rowid => l_row_id,
X_Balance_Feed_Id => l_Balance_Feed_Id,
X_Effective_Start_Date => p_effective_start_date,
X_Effective_End_Date => hr_api.g_eot,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => null,
X_Balance_Type_Id => l_seed_ee_bal_type_id,
X_Input_Value_Id => ipv_rec.input_value_id,
X_Scale => '1',
X_Legislation_Subgroup => null,
X_Initial_Balance_Feed => false );
Pay_Balance_Feeds_f_pkg.Insert_Row(
X_Rowid => l_row_id,
X_Balance_Feed_Id => l_Balance_Feed_Id,
X_Effective_Start_Date => p_effective_start_date,
X_Effective_End_Date => hr_api.g_eot,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => null,
X_Balance_Type_Id => l_seed_ee_bal_type_id,
X_Input_Value_Id => ipv_rec.input_value_id,
X_Scale => '1',
X_Legislation_Subgroup => null,
X_Initial_Balance_Feed => false );
Pay_Balance_Feeds_f_pkg.Insert_Row(
X_Rowid => l_row_id,
X_Balance_Feed_Id => l_Balance_Feed_Id,
X_Effective_Start_Date => p_effective_start_date,
X_Effective_End_Date => hr_api.g_eot,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => null,
X_Balance_Type_Id => l_seed_er_bal_type_id,
X_Input_Value_Id => ipv_rec.input_value_id,
X_Scale => '1',
X_Legislation_Subgroup => null,
X_Initial_Balance_Feed => false );
Pay_Balance_Feeds_f_pkg.Insert_Row(
X_Rowid => l_row_id,
X_Balance_Feed_Id => l_Balance_Feed_Id,
X_Effective_Start_Date => p_effective_start_date,
X_Effective_End_Date => hr_api.g_eot,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => null,
X_Balance_Type_Id => l_seed_er_bal_type_id,
X_Input_Value_Id => ipv_rec.input_value_id,
X_Scale => '1',
X_Legislation_Subgroup => null,
X_Initial_Balance_Feed => false );
procedure Delete_User_Template
(p_business_group_id In Number
,p_pension_dedn_ele_name In Varchar2
,p_pension_dedn_ele_type_id In Number
,p_security_group_id In Number
,p_effective_date In Date
) is
--
cursor c1 is
select template_id
from pay_element_templates
where base_name||' Pension Deduction' = p_pension_dedn_ele_name
and business_group_id = p_business_group_id
and template_type = 'U';
SELECT element_type_extra_info_id
,object_version_number
FROM pay_element_type_extra_info
WHERE eei_information_category = 'IE_PENSION_SCHEME_INFO'
AND element_type_id = p_pension_dedn_ele_type_id;
l_proc Varchar2(60) := g_proc_name||'Delete_User_Template';
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
pay_element_extra_info_api.delete_element_extra_info
(p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
,p_object_version_number => temp_rec.object_version_number);
end Delete_User_Template;
procedure Delete_User_Template_Swi
(p_business_group_id In Number
,p_pension_dedn_ele_name In Varchar2
,p_pension_dedn_ele_type_id In Number
,p_security_group_id In Number
,p_effective_date In Date
) is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := 'Delete_User_Template_Swi';
savepoint Delete_User_Template_Swi;
Delete_User_Template
(p_business_group_id => p_business_group_id
,p_pension_dedn_ele_name => p_pension_dedn_ele_name
,p_pension_dedn_ele_type_id => p_pension_dedn_ele_type_id
,p_security_group_id => p_security_group_id
,p_effective_date => p_effective_date
);
rollback to Delete_User_Template_Swi;
rollback to Delete_User_Template_Swi;
END delete_user_template_swi;