[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';
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 = 'NL' 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 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
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 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 pension_salary_balance
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 formula_text
from pay_shadow_formulas
where formula_id = c_formula_id
and template_type = 'U';
select user_name
from ff_database_items dbi
,ff_route_parameter_values rpv
,ff_route_parameters rp
,pay_balance_dimensions pbd
,pay_defined_balances pdb
where dbi.user_entity_id = rpv.user_entity_id
and rpv.route_parameter_id = rp.route_parameter_id
and rp.route_id = pbd.route_id
AND pbd.database_item_suffix = '_PER_YTD'
and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
and pdb.balance_type_id = to_char(c_bal_type_id)
and pbd.legislation_code = 'NL'
AND rpv.value = pdb.DEFINED_BALANCE_ID;
select user_name
from ff_database_items dbi
,ff_route_parameter_values rpv
,ff_route_parameters rp
,pay_balance_dimensions pbd
,pay_defined_balances pdb
where dbi.user_entity_id = rpv.user_entity_id
and rpv.route_parameter_id = rp.route_parameter_id
and rp.route_id = pbd.route_id
AND pbd.database_item_suffix = '_ASG_RUN'
and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
and pdb.balance_type_id = to_char(c_bal_type_id)
and pbd.legislation_code = 'NL'
AND rpv.value = pdb.DEFINED_BALANCE_ID ;
select balance_name
from pay_balance_types
where balance_type_id = c_bal_type_id
and (business_group_id = p_business_group_id
OR business_group_id is null
OR legislation_code = 'NL');
SELECT 'x'
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS'
AND eei_information1 = p_scheme_description
AND rownum = 1;
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;
select bc.classification_id, pbf.input_value_id,
pbf.scale, pbf.element_type_id
from pay_balance_feeds_v pbf,
pay_balance_classifications bc,
pay_element_classifications pec,
pay_element_classifications_tl pect,
pay_balance_types_tl pbtl
where nvl(pbf.balance_initialization_flag,'N') = 'N'
and nvl(pbf.business_group_id,
p_business_group_id) = p_business_group_id
and nvl(pbf.legislation_code, 'NL') = 'NL'
and pbtl.balance_name = 'Gross Salary'
and pbtl.language = 'US'
and pbtl.balance_type_id = pbf.balance_type_id
and bc.balance_type_id = pbf.balance_type_id
and pec.classification_id = pect.classification_id
and bc.classification_id = pec.classification_id
and pect.classification_name = 'Earnings'
and pect.language = 'US'
and nvl(pec.legislation_code, 'NL') = 'NL'
order by pbf.element_name;
select balance_type_id
from pay_balance_types
where business_group_id = p_business_group_id
and balance_name in (p_scheme_prefix||' Pension Salary');
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 => c2_rec.balance_type_id,
X_Input_Value_Id => c1_rec.input_value_id,
X_Scale => c1_rec.scale,
X_Legislation_Subgroup => null,
X_Initial_Balance_Feed => false );
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 => nvl(l_std_link_flag, hr_api.g_varchar2)
,p_object_version_number => l_shadow_element(i).object_version_number
);
update pay_shadow_formulas
set formula_text = l_formula_text
where formula_id = l_shad_formula_id
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text
where formula_id = l_shad_formula_id
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text
where formula_id = l_shad_formula_id
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text
where formula_id = l_shad_formula_id
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text
where formula_id = l_shad_formula_id
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text1
where formula_id = l_shad_formula_id1
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text1
where formula_id = l_shad_formula_id1
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text1
where formula_id = l_shad_formula_id1
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text1
where formula_id = l_shad_formula_id1
and business_group_id = p_business_group_id;
update pay_shadow_formulas
set formula_text = l_formula_text1
where formula_id = l_shad_formula_id1
and business_group_id = p_business_group_id;
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Pension Type Id'
,to_char(p_pension_type_id)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Employer Pension Contribution'
,'Pension Type Id'
,to_char(p_pension_type_id)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Percentage'
,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Fixed Premium Amount'
,fnd_number.number_to_canonical(r_pty_rec.annual_premium_amount)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Pension Deduction'
,'Percentage'
,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
,p_business_group_id);
Update_Ipval_Defval( p_scheme_prefix||' Employer Pension Contribution'
,'Percentage'
,fnd_number.number_to_canonical(r_pty_rec.er_contribution_percent)
,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_ee_contribution_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_er_contribution_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_pension_plan_id in Number
,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 = 'PQP_NL_PRE_TAX_DEDUCTIONS'
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_pension_plan_id in Number
,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_pension_plan_id => p_pension_plan_id
,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;