[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 *
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' -- nilesh
and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
AND pbd.legislation_code = 'NL'
and pdb.balance_type_id = to_char(c_bal_type_id)
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 -- 4472436
AND rpv.route_parameter_id = rp.route_parameter_id -- 1999
AND rp.route_id = pbd.route_id
AND pbd.database_item_suffix = '_ASG_RUN' -- nilesh
AND pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
AND pbd.legislation_code = 'NL'
AND pdb.balance_type_id = to_char(c_bal_type_id)
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 balance_type_id
FROM pay_balance_types_tl
WHERE balance_name = 'Retro '||c_subcat||' '
||c_ee_er||' Contribution'
AND LANGUAGE = 'US';
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 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;
PROCEDURE Update_Input_Vaue_Col ( c_element_type_id IN NUMBER,
c_iv_name IN VARCHAR2,
c_business_group_id IN NUMBER,
c_mandatory_flag IN CHAR
)
IS
l_proc_name VARCHAR2(72) := g_proc_name || 'Update_Input_Vaue_Col';
update pay_input_values_f
set mandatory_flag = c_mandatory_flag
where element_type_id =c_element_type_id
and name = c_iv_name
and business_group_id = c_business_group_id;
END Update_Input_Vaue_Col;
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_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_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_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_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;
UPDATE pay_shadow_formulas
SET formula_text = l_formula_text
WHERE formula_id = l_shad_formula_id1
AND business_group_id = p_business_group_id;
pqp_nl_pension_template.Update_Ipval_Defval(
p_scheme_prefix||' Employee 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||' Employee Savings Deduction'
,'Contribution Type'
,p_ee_deduction_method
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval(
p_scheme_prefix||' Employee Savings Deduction'
,'Enable Maximum Deduction'
,'N'
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval(
p_scheme_prefix||' Employee Savings Deduction'
,'Enable Limit Check'
,'Y'
,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||' Employer Savings Contribution'
,'Contribution Type'
,p_er_deduction_method
,p_business_group_id);
Update_Input_Vaue_Col(l_base_element_type_id,
'Contribution Type' ,
p_business_group_id ,
'X');
Update_Input_Vaue_Col(l_er_base_element_type_id,
'Contribution Type' ,
p_business_group_id ,
'X');
Update_Event_Group
(p_element_name => p_scheme_prefix||' Employee 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||' Employee 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;