[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT element_type_id
INTO l_creator_id
FROM pay_element_types_f
WHERE element_name = p_creator_name
AND business_group_id = p_business_group_id;
SELECT classification_id
INTO l_creator_id
FROM pay_element_classifications
WHERE classification_name = p_creator_name
AND business_group_id = p_business_group_id;
SELECT rc.retro_component_id,rc.component_name, rc.retro_type
INTO l_comp_id, l_comp_name, l_comp_type
FROM pay_retro_definitions rd,
pay_retro_defn_components rdc,
pay_retro_components rc
WHERE rdc.retro_component_id = rc.retro_component_id
AND rc.legislation_code = g_legislation_code
AND rdc.priority = p_retro_component_priority
AND rd.retro_definition_id = rdc.retro_definition_id
AND rd.legislation_code = g_legislation_code
AND rd.definition_name = g_retro_def_name;
SELECT Retro_Component_Usage_Id
INTO l_rc_usage_id
FROM pay_retro_component_usages
WHERE retro_component_id = l_comp_id
AND creator_id = l_creator_id
AND creator_type = p_creator_type;
SELECT pay_retro_component_usages_s.NEXTVAL
INTO l_rc_usage_id
FROM dual;
hr_utility.set_location('Insert Retro Comp Usgs '||l_proc_name, 20);
INSERT INTO pay_retro_component_usages(
RETRO_COMPONENT_USAGE_ID,
RETRO_COMPONENT_ID,
CREATOR_ID,
CREATOR_TYPE,
DEFAULT_COMPONENT,
REPROCESS_TYPE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(l_rc_usage_id
,l_comp_id
,l_creator_id
,p_creator_type
,p_default_component
,p_reprocess_type
,p_business_group_id
,NULL
,SYSDATE
,-1
,SYSDATE
,-1
,-1
,1);
SELECT ts.time_span_id
INTO l_time_span_id
FROM pay_time_definitions s,
pay_time_definitions e,
pay_time_spans ts
WHERE ts.creator_id = l_comp_id
AND ts.creator_type = 'RC'
AND ts.start_time_def_id = s.time_definition_id
AND ts.end_time_def_id = e.time_definition_id
AND s.legislation_code = 'NL'
AND s.definition_name = p_start_time_def_name
AND e.legislation_code = 'NL'
AND e.definition_name = p_end_time_def_name;
SELECT DISTINCT element_type_id
INTO l_retro_ele_id
FROM pay_element_types_f
WHERE element_name = p_retro_element_name
AND business_group_id = p_business_group_id;
SELECT element_span_usage_id
INTO l_es_usage_id
FROM pay_element_span_usages
WHERE time_span_id = l_time_span_id
AND retro_component_usage_id = l_rc_usage_id
AND adjustment_type IS NULL;
SELECT pay_element_span_usages_s.NEXTVAL
INTO l_es_usage_id
FROM dual;
hr_utility.set_location('Insert Element Span Usgs '||l_proc_name, 30);
INSERT INTO pay_element_span_usages(
ELEMENT_SPAN_USAGE_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
TIME_SPAN_ID,
RETRO_COMPONENT_USAGE_ID,
ADJUSTMENT_TYPE,
RETRO_ELEMENT_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES(l_es_usage_id
,p_business_group_id
,NULL
,l_time_span_id
,l_rc_usage_id
,NULL
,l_retro_ele_id
,SYSDATE
,-1
,SYSDATE
,-1
,-1
,1);
PROCEDURE Update_Event_Group
(p_element_name VARCHAR2,
p_business_group_id NUMBER)
IS
CURSOR c_get_retro_evg_id IS
SELECT event_group_id
FROM pay_event_groups
WHERE event_group_name = 'PQP_NL_RETRO_EVG'
AND legislation_code = 'NL';
l_proc_name := g_proc_name||'Update_Event_Group';
UPDATE pay_element_types_f
SET recalc_event_group_id = l_retro_evg_id
WHERE element_name = p_element_name
AND business_group_id = p_business_group_id;
END Update_Event_Group;
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 input_value_id
from pay_shadow_input_values
where element_type_id =
(Select element_type_id
from pay_shadow_element_types
where template_id = l_template_id
and element_name = c_shd_elename)
and name = 'Percentage';
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 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 'x'
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_SAVINGS_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
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 pbf.balance_name = 'Gross Salary'
and bc.balance_type_id = pbf.balance_type_id
and bc.classification_id = pec.classification_id
and pec.classification_name = 'Earnings'
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_input_values
SET mandatory_flag = 'Y'
WHERE input_value_id = csr_rec.input_value_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_text
WHERE formula_id = l_shad_formula_id
AND business_group_id = p_business_group_id;
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Savings Deduction'
,'Pension Type Id'
,to_char(p_pension_type_id)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Employer Savings Contribution'
,'Pension Type Id'
,to_char(p_pension_type_id)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Savings Deduction'
,'Percentage'
,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Savings Deduction'
,'Fixed Premium Amount'
,fnd_number.number_to_canonical(r_pty_rec.annual_premium_amount)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Savings Deduction'
,'Percentage'
,fnd_number.number_to_canonical(r_pty_rec.ee_contribution_percent)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval( p_scheme_prefix||' Employer Savings 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 );
Update_Event_Group
(p_element_name => p_scheme_prefix||' Savings Deduction'
,p_business_group_id => p_business_group_id);
Update_Event_Group
(p_element_name => p_scheme_prefix||' Employer Savings Contribution'
,p_business_group_id => p_business_group_id);
procedure Delete_User_Template
(p_savings_plan_id in Number
,p_business_group_id in Number
,p_savings_dedn_ele_name in Varchar2
,p_savings_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||' Savings Deduction' = p_savings_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_SAVINGS_DEDUCTIONS'
AND element_type_id = p_savings_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_savings_plan_id in Number
,p_business_group_id in Number
,p_savings_dedn_ele_name in Varchar2
,p_savings_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_savings_plan_id => p_savings_plan_id
,p_business_group_id => p_business_group_id
,p_savings_dedn_ele_name => p_savings_dedn_ele_name
,p_savings_dedn_ele_type_id => p_savings_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;