[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'
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_ABP_DEDUCTION'
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 decode(c_subcat
,'OPNP','OPNP'
,'OPNP_65','OPNP65'
,'OPNP_AOW','OPNPAOW'
,'OPNP_W25','OPNPW25'
,'OPNP_W50','OPNPW50'
,'FPU_E','FPU Extra'
,'FPU_R','FPU Raise'
,'FPU_S','FPU Standard'
,'FPU_T','FPU Total'
,'FUR_S','FUR Standard'
,'IPAP','IPAP'
,'IPBW_H','IPBW High'
,'IPBW_L','IPBW Low'
,'VSG','VSG'
,'FPU_B','FPU Base'
,'FPU_C','FPU Composition'
,'PPP','Partner Plus Pension'
,'FPB','FP Basis'
,'AAOP','ABP Disabiliy Pension'
,c_subcat
)
FROM dual;
select distinct element_type_id
into l_creator_id
from pay_element_types_f
where element_name = p_creator_name
-- and legislation_code = g_legislation_code
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 legislation_code = g_legislation_code
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;
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;
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);
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 );
SELECT to_date(pei.eei_information10,'DD/MM/YYYY') date_from
,to_date(pei.eei_information11,'DD/MM/YYYY') date_to
,pei.eei_information1 scheme_name
FROM pay_element_type_extra_info pei
WHERE pei.eei_information12 = p_pension_sub_category
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND EXISTS( SELECT 1
FROM pay_element_types_f pet
WHERE pei.element_type_id = pet.element_type_id
AND pet.business_group_id = p_business_group_id);
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 psp.status_processing_rule_id
FROM pay_status_processing_rules_f psp
,pay_element_types_f pet
,ff_formulas_f fff
WHERE psp.element_type_id = pet.element_type_id
AND psp.formula_id = fff.formula_id
AND trunc(p_scheme_start_date)
BETWEEN psp.effective_start_date AND psp.effective_end_date
AND trunc(p_scheme_start_date)
BETWEEN pet.effective_start_date AND pet.effective_end_date
AND trunc(p_scheme_start_date)
BETWEEN fff.effective_start_date AND fff.effective_end_date
AND pet.element_name = 'ABP Pensions'
AND pet.legislation_code = 'NL'
AND fff.formula_name = 'ABP_PENSION_INFORMATION'
AND fff.legislation_code = 'NL'
AND psp.business_group_id = p_business_group_id;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'ABP Pensions'
AND legislation_code = 'NL'
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = 'ABP_PENSION_INFORMATION'
AND legislation_code = 'NL'
AND trunc(p_scheme_start_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT decode(p_pension_sub_category,'FPU_B','FB',
'FPU_C','FC','FPU_E','FE','FPU_R','FR',
'FPU_S','FS','FPU_T','FT','FUR_S','FUS',
'IPAP','I','IPBW_L','IL','IPBW_H','IH',
'OPNP','O','OPNP_65','O65','OPNP_AOW','OA',
'OPNP_W25','OW25','OPNP_W50','OW50',
'PPP','P','VSG','V','FPB','FP','AAOP','AP')
FROM dual;
pay_status_rules_pkg.Insert_Row
( X_Rowid => l_rowid
,X_Status_Processing_Rule_Id => l_status_processing_rule_id
,X_Effective_Start_Date => to_date('01/01/1951','DD/MM/RRRR')
,X_Effective_End_Date => to_date('31/12/4712','DD/MM/RRRR')
,X_Business_Group_Id => p_business_group_id
,X_Legislation_Code => NULL
,X_Element_Type_Id => l_abp_ele_id
,X_Assignment_Status_Type_Id => NULL
,X_Formula_Id => l_abp_formula_id
,X_Processing_Rule => 'P'
,X_Comment_Id => NULL
,X_Legislation_Subgroup => NULL
,X_Last_Update_Date => hr_api.g_sys
,X_Last_Updated_By => -1
,X_Last_Update_Login => -1
,X_Created_By => -1
,X_Creation_Date => hr_api.g_sys);
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 => p_scheme_start_date
,p_effective_end_date => p_scheme_end_date
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_element_type_id => p_abp_element_type_id
,p_status_processing_rule_id => l_status_processing_rule_id
,p_result_name => l_input_result(i).result_name
,p_result_rule_type => 'I'
,p_legislation_subgroup => NULL
,p_severity_level => NULL
,p_input_value_id => l_input_result(i).input_value_id
,p_session_date => p_scheme_start_date
,p_created_by => -1
);
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_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;
pqp_nl_pension_template.Update_Ipval_Defval(
p_scheme_prefix||' ABP Pension Deduction'
,'Pension Type Id'
,TO_CHAR(p_pension_type_id)
,p_business_group_id);
pqp_nl_pension_template.Update_Ipval_Defval(
p_scheme_prefix||' ABP Employer Pension Contribution'
,'Pension Type Id'
,TO_CHAR(p_pension_type_id)
,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_ee_retro_bal_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_ee_retro_bal_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 );
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_retro_bal_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_retro_bal_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
,base_name
FROM pay_element_templates
WHERE base_name||' ABP 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 ovn
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND element_type_id = p_pension_dedn_ele_type_id;
SELECT 1
FROM dual
WHERE EXISTS ( SELECT 1
FROM pay_run_results prr
WHERE prr.element_type_id = c_element_type_id) ;
SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = p_business_group_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND ppa.effective_date >= trunc(p_effective_date)
AND EXISTS ( SELECT 1
FROM pay_run_results prr
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = c_in_ele_typ_id) ;
SELECT formula_result_rule_id
,rowid
FROM pay_formula_result_rules_f
WHERE element_type_id = c_ele_typ_id
AND result_rule_type = 'I'
AND business_group_id = p_business_group_id
FOR UPDATE OF effective_end_date;
SELECT retro_component_usage_id
FROM pay_retro_component_usages
WHERE creator_id = c_element_type_id
AND creator_type = 'ET'
AND business_group_id = p_business_group_id;
SELECT element_span_usage_id
FROM pay_element_span_usages
WHERE retro_component_usage_id = c_retro_comp_usage_id
AND business_group_id = p_business_group_id;
l_proc VARCHAR2(60) := g_proc_name||'Delete_User_Template';
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = c_base_name||' ABP Employer Pension Contribution'
AND business_group_id = p_business_group_id
AND trunc(p_effective_date) BETWEEN effective_start_date AND
effective_end_date ;
pay_formula_result_rules_pkg.delete_row(p_rowid => temp_rec.rowid);
pay_formula_result_rules_pkg.delete_row(p_rowid => temp_rec.rowid);
DELETE
FROM pay_element_span_usages
WHERE element_span_usage_id = temp_rec.element_span_usage_id;
DELETE
FROM pay_retro_component_usages
WHERE retro_component_usage_id = l_retro_comp_id;
DELETE
FROM pay_element_span_usages
WHERE element_span_usage_id = temp_rec.element_span_usage_id;
DELETE
FROM pay_retro_component_usages
WHERE retro_component_usage_id = l_retro_comp_id;
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.ovn);
UPDATE pay_formula_result_rules_f
SET effective_end_date = p_effective_date
WHERE formula_result_rule_id = temp_rec.formula_result_rule_id;
UPDATE pay_formula_result_rules_f
SET effective_end_date = p_effective_date
WHERE formula_result_rule_id = temp_rec.formula_result_rule_id;
pay_element_extra_info_api.update_element_extra_info
(p_validate => FALSE
,p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
,p_object_version_number => temp_rec.ovn
,p_eei_information_category => 'PQP_NL_ABP_DEDUCTION'
,p_eei_information11 => to_char(p_effective_date,'DD/MM/YYYY')
);
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;