[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select currency_code,legislation_code,name
from per_business_groups_perf
where business_group_id = p_business_group_id;
Select 'x'
from pay_balance_types
where balance_name = c_balance_name
and business_group_id = p_business_group_id;
Select balance_dimension_id
from pay_balance_dimensions
where legislation_code = c_legislation_code
and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
'_ASG_MONTH', '_ASG_PTD',
'_ASG_YTD', '_ASG_RUN',
'_PER_ITD', '_PER_PTD',
'_PER_QTD', '_PER_YTD'
);
Select balance_category_id
from pay_balance_categories_f
where category_name = c_category_name
and legislation_code = c_legislation_code
and c_effective_date between effective_start_date and effective_end_date;
Pay_Balance_Types_pkg.Insert_Row
(x_Rowid => l_row_id
,x_Balance_Type_Id => l_balance_type_id
,x_Business_Group_Id => p_business_group_id
,x_Legislation_Code => p_legislation_code
,x_Currency_Code => l_currency_code
,x_Assignment_Remuneration_Flag => 'N'
,x_Balance_Name => l_balance_name
,x_Base_Balance_Name => l_balance_name
,x_Balance_Uom => 'M'
,x_Comments => Null
,x_Legislation_Subgroup => Null
,x_Reporting_Name => l_reporting_name
,x_Attribute_Category => Null
,X_Attribute1 => Null
,x_Attribute2 => Null
,x_Attribute3 => Null
,x_Attribute4 => Null
,x_Attribute5 => Null
,x_Attribute6 => Null
,x_Attribute7 => Null
,x_Attribute8 => Null
,x_Attribute9 => Null
,x_Attribute10 => Null
,x_Attribute11 => Null
,x_Attribute12 => Null
,x_Attribute13 => Null
,x_Attribute14 => Null
,x_Attribute15 => Null
,x_Attribute16 => Null
,x_Attribute17 => Null
,x_Attribute18 => Null
,x_Attribute19 => Null
,x_Attribute20 => Null
,x_balance_category_id => l_balance_cat_id
,x_base_balance_type_id => Null
,x_input_value_id => Null
);
Pay_Defined_Balances_pkg.Insert_Row
(x_rowid => l_row_id
,x_defined_balance_id => l_defined_balance_id
,x_business_group_id => p_business_group_id
,x_legislation_code => p_legislation_code
,x_balance_type_id => l_balance_type_id
,x_balance_dimension_id => csr_dim.balance_dimension_id
,x_force_latest_balance_flag => 'N'
,x_legislation_subgroup => null
,x_grossup_allowed_flag => 'N'
);
Select currency_code,legislation_code,name
from per_business_groups_perf
where business_group_id = p_business_group_id;
Select 'x'
from pay_balance_types
where balance_name = c_balance_name
and business_group_id = p_business_group_id;
Select balance_dimension_id
from pay_balance_dimensions
where legislation_code = c_legislation_code
and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
'_ASG_MONTH', '_ASG_PTD',
'_ASG_YTD', '_ASG_RUN',
'_PER_ITD', '_PER_PTD',
'_PER_QTD', '_PER_YTD'
);
Select balance_category_id
from pay_balance_categories_f
where category_name = c_category_name
and legislation_code = c_legislation_code
and c_effective_date between effective_start_date and effective_end_date;
Pay_Balance_Types_pkg.Insert_Row
(x_Rowid => l_row_id
,x_Balance_Type_Id => l_balance_type_id
,x_Business_Group_Id => p_business_group_id
,x_Legislation_Code => p_legislation_code
,x_Currency_Code => l_currency_code
,x_Assignment_Remuneration_Flag => 'N'
,x_Balance_Name => l_balance_name
,x_Base_Balance_Name => l_balance_name
,x_Balance_Uom => 'M'
,x_Comments => Null
,x_Legislation_Subgroup => Null
,x_Reporting_Name => l_reporting_name
,x_Attribute_Category => Null
,X_Attribute1 => Null
,x_Attribute2 => Null
,x_Attribute3 => Null
,x_Attribute4 => Null
,x_Attribute5 => Null
,x_Attribute6 => Null
,x_Attribute7 => Null
,x_Attribute8 => Null
,x_Attribute9 => Null
,x_Attribute10 => Null
,x_Attribute11 => Null
,x_Attribute12 => Null
,x_Attribute13 => Null
,x_Attribute14 => Null
,x_Attribute15 => Null
,x_Attribute16 => Null
,x_Attribute17 => Null
,x_Attribute18 => Null
,x_Attribute19 => Null
,x_Attribute20 => Null
,x_balance_category_id => l_balance_cat_id
,x_base_balance_type_id => Null
,x_input_value_id => Null
);
Pay_Defined_Balances_pkg.Insert_Row
(x_rowid => l_row_id
,x_defined_balance_id => l_defined_balance_id
,x_business_group_id => p_business_group_id
,x_legislation_code => p_legislation_code
,x_balance_type_id => l_balance_type_id
,x_balance_dimension_id => csr_dim.balance_dimension_id
,x_force_latest_balance_flag => 'N'
,x_legislation_subgroup => null
,x_grossup_allowed_flag => 'N'
);
Select currency_code,legislation_code,name
from per_business_groups_perf
where business_group_id = p_business_group_id;
Select 'x'
from pay_balance_types
where balance_name = c_balance_name
and business_group_id = p_business_group_id;
Select balance_dimension_id
from pay_balance_dimensions
where legislation_code = c_legislation_code
and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
'_ASG_MONTH', '_ASG_PTD',
'_ASG_YTD', '_ASG_RUN',
'_PER_ITD', '_PER_PTD',
'_PER_QTD', '_PER_YTD'
);
Select balance_category_id
from pay_balance_categories_f
where category_name = c_category_name
and legislation_code = c_legislation_code
and c_effective_date between effective_start_date and effective_end_date;
Pay_Balance_Types_pkg.Insert_Row
(x_Rowid => l_row_id
,x_Balance_Type_Id => l_balance_type_id
,x_Business_Group_Id => p_business_group_id
,x_Legislation_Code => p_legislation_code
,x_Currency_Code => l_currency_code
,x_Assignment_Remuneration_Flag => 'N'
,x_Balance_Name => l_balance_name
,x_Base_Balance_Name => l_balance_name
,x_Balance_Uom => 'M'
,x_Comments => Null
,x_Legislation_Subgroup => Null
,x_Reporting_Name => l_reporting_name
,x_Attribute_Category => Null
,X_Attribute1 => Null
,x_Attribute2 => Null
,x_Attribute3 => Null
,x_Attribute4 => Null
,x_Attribute5 => Null
,x_Attribute6 => Null
,x_Attribute7 => Null
,x_Attribute8 => Null
,x_Attribute9 => Null
,x_Attribute10 => Null
,x_Attribute11 => Null
,x_Attribute12 => Null
,x_Attribute13 => Null
,x_Attribute14 => Null
,x_Attribute15 => Null
,x_Attribute16 => Null
,x_Attribute17 => Null
,x_Attribute18 => Null
,x_Attribute19 => Null
,x_Attribute20 => Null
,x_balance_category_id => l_balance_cat_id
,x_base_balance_type_id => Null
,x_input_value_id => Null
);
Pay_Defined_Balances_pkg.Insert_Row
(x_rowid => l_row_id
,x_defined_balance_id => l_defined_balance_id
,x_business_group_id => p_business_group_id
,x_legislation_code => p_legislation_code
,x_balance_type_id => l_balance_type_id
,x_balance_dimension_id => csr_dim.balance_dimension_id
,x_force_latest_balance_flag => 'N'
,x_legislation_subgroup => null
,x_grossup_allowed_flag => 'N'
);
Select currency_code,legislation_code,name
from per_business_groups_perf
where business_group_id = p_business_group_id;
Select balance_type_id
from pay_balance_types
where balance_name = c_balance_name
and business_group_id = p_business_group_id;
Select balance_dimension_id
from pay_balance_dimensions
where legislation_code = c_legislation_code
and database_item_suffix in ('_ASG_ITD', '_ASG_QTD',
'_ASG_MONTH', '_ASG_PTD',
'_ASG_YTD', '_ASG_RUN',
'_PER_ITD', '_PER_PTD',
'_PER_QTD', '_PER_YTD'
);
Select DECODE(p_pension_sub_cat
,'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 Disability'
,l_pension_sub_cat)
INTO l_pension_sub_cat
From dual;
Pay_Balance_Types_pkg.Insert_Row
(x_Rowid => l_row_id
,x_Balance_Type_Id => l_balance_type_id
,x_Business_Group_Id => p_business_group_id
,x_Legislation_Code => p_legislation_code
,x_Currency_Code => l_currency_code
,x_Assignment_Remuneration_Flag => 'N'
,x_Balance_Name => l_balance_name
,x_Base_Balance_Name => l_balance_name
,x_Balance_Uom => 'M'
,x_Comments => Null
,x_Legislation_Subgroup => Null
,x_Reporting_Name => l_reporting_name
,x_Attribute_Category => Null
,X_Attribute1 => Null
,x_Attribute2 => Null
,x_Attribute3 => Null
,x_Attribute4 => Null
,x_Attribute5 => Null
,x_Attribute6 => Null
,x_Attribute7 => Null
,x_Attribute8 => Null
,x_Attribute9 => Null
,x_Attribute10 => Null
,x_Attribute11 => Null
,x_Attribute12 => Null
,x_Attribute13 => Null
,x_Attribute14 => Null
,x_Attribute15 => Null
,x_Attribute16 => Null
,x_Attribute17 => Null
,x_Attribute18 => Null
,x_Attribute19 => Null
,x_Attribute20 => Null
,x_balance_category_id => l_balance_cat_id
,x_base_balance_type_id => Null
,x_input_value_id => Null
);
Pay_Defined_Balances_pkg.Insert_Row
(x_rowid => l_row_id
,x_defined_balance_id => l_defined_balance_id
,x_business_group_id => p_business_group_id
,x_legislation_code => p_legislation_code
,x_balance_type_id => l_balance_type_id
,x_balance_dimension_id => csr_dim.balance_dimension_id
,x_force_latest_balance_flag => 'N'
,x_legislation_subgroup => null
,x_grossup_allowed_flag => 'N'
);
Select balance_type_id
from pay_balance_types
where balance_name = c_balance_name
and (business_group_id = p_business_group_id
or
legislation_code = g_leg_code);
Select DECODE(p_pension_sub_cat
,'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'
,l_pension_sub_cat_mean)
INTO l_pension_sub_cat_mean
From dual;
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_date,
X_Effective_End_Date => hr_api.g_eot,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => p_legislation_code,
X_Balance_Type_Id => l_balfeeds_tab(i).balance_type_id,
X_Input_Value_Id => l_balfeeds_tab(i).input_value_id,
X_Scale => '1',
X_Legislation_Subgroup => Null,
X_Initial_Balance_Feed => false );
SELECT 'x'
FROM pqp_pension_types_f
WHERE pension_type_name = p_pension_type_name_in
AND business_group_id = p_bg_id
AND rownum = 1;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
Procedure Update_Pension_Type
(p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_pension_type_id in number
,p_object_version_number in out nocopy number
,p_pension_type_name in varchar2 --x
,p_pension_category in varchar2 --x
,p_pension_provider_type in varchar2
,p_salary_calculation_method in varchar2
,p_threshold_conversion_rule in varchar2
,p_contribution_conversion_rule in varchar2
,p_er_annual_limit in number
,p_ee_annual_limit in number
,p_er_annual_salary_threshold in number
,p_ee_annual_salary_threshold in number
,p_business_group_id in number --x
,p_legislation_code in varchar2 --x
,p_description in varchar2
,p_minimum_age in number
,p_ee_contribution_percent in number
,p_maximum_age in number
,p_er_contribution_percent in number
,p_ee_annual_contribution in number
,p_er_annual_contribution in number
,p_annual_premium_amount in number
,p_ee_contribution_bal_type_id in number --x
,p_er_contribution_bal_type_id in number --x
,p_balance_init_element_type_id in number --x
,p_ee_contribution_fixed_rate in number --added for UK
,p_er_contribution_fixed_rate in number --added for UK
,p_pty_attribute_category in varchar2
,p_pty_attribute1 in varchar2
,p_pty_attribute2 in varchar2
,p_pty_attribute3 in varchar2
,p_pty_attribute4 in varchar2
,p_pty_attribute5 in varchar2
,p_pty_attribute6 in varchar2
,p_pty_attribute7 in varchar2
,p_pty_attribute8 in varchar2
,p_pty_attribute9 in varchar2
,p_pty_attribute10 in varchar2
,p_pty_attribute11 in varchar2
,p_pty_attribute12 in varchar2
,p_pty_attribute13 in varchar2
,p_pty_attribute14 in varchar2
,p_pty_attribute15 in varchar2
,p_pty_attribute16 in varchar2
,p_pty_attribute17 in varchar2
,p_pty_attribute18 in varchar2
,p_pty_attribute19 in varchar2
,p_pty_attribute20 in varchar2
,p_pty_information_category in varchar2
,p_pty_information1 in varchar2
,p_pty_information2 in varchar2
,p_pty_information3 in varchar2
,p_pty_information4 in varchar2
,p_pty_information5 in varchar2
,p_pty_information6 in varchar2
,p_pty_information7 in varchar2
,p_pty_information8 in varchar2
,p_pty_information9 in varchar2
,p_pty_information10 in varchar2
,p_pty_information11 in varchar2
,p_pty_information12 in varchar2
,p_pty_information13 in varchar2
,p_pty_information14 in varchar2
,p_pty_information15 in varchar2
,p_pty_information16 in varchar2
,p_pty_information17 in varchar2
,p_pty_information18 in varchar2
,p_pty_information19 in varchar2
,p_pty_information20 in varchar2
,p_special_pension_type_code in varchar2 -- added for NL Phase 2B
,p_pension_sub_category in varchar2 -- added for NL Phase 2B
,p_pension_basis_calc_method in varchar2 -- added for NL Phase 2B
,p_pension_salary_balance in number -- added for NL Phase 2B
,p_recurring_bonus_percent in number -- added for NL Phase 2B
,p_non_recurring_bonus_percent in number -- added for NL Phase 2B
,p_recurring_bonus_balance in number -- added for NL Phase 2B
,p_non_recurring_bonus_balance in number -- added for NL Phase 2B
,p_std_tax_reduction in varchar2 -- added for NL Phase 2B
,p_spl_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sig_sal_spl_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sig_sal_non_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sig_sal_std_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sii_std_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sii_spl_tax_reduction in varchar2 -- added for NL Phase 2B
,p_sii_non_tax_reduction in varchar2 -- added for NL Phase 2B
,p_previous_year_bonus_included in varchar2 -- added for NL Phase 2B
,p_recurring_bonus_period in varchar2 -- added for NL Phase 2B
,p_non_recurring_bonus_period in varchar2 -- added for NL Phase 2B
,p_ee_age_threshold in varchar2 -- added for ABP TAR fixes
,p_er_age_threshold in varchar2 -- added for ABP TAR fixes
,p_ee_age_contribution in varchar2 -- added for ABP TAR fixes
,p_er_age_contribution in varchar2 -- added for ABP TAR fixes
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_api_warning out nocopy varchar2
) Is
l_proc varchar2(150) := g_package||'Update_Pension_Type';
SELECT 1
FROM pay_element_type_extra_info
WHERE information_type = 'HU_PENSION_SCHEME_INFO'
AND eei_information_category = 'HU_PENSION_SCHEME_INFO'
AND eei_information3 = fnd_number.number_to_canonical(p_pension_type_id);
SELECT pension_category
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
savepoint Update_Pension_Type;
hr_utility.set_location('Before Calling User Hook Update_Pension_Type_b',20);
PQP_Pension_Types_BK2.Update_Pension_Type_b
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pension_type_id => p_pension_type_id
,p_object_version_number => l_object_version_number
,p_pension_type_name => p_pension_type_name
,p_pension_category => p_pension_category
,p_pension_provider_type => p_pension_provider_type
,p_salary_calculation_method => p_salary_calculation_method
,p_threshold_conversion_rule => p_threshold_conversion_rule
,p_contribution_conversion_rule => p_contribution_conversion_rule
,p_er_annual_limit => l_er_annual_limit
,p_ee_annual_limit => l_ee_annual_limit
,p_er_annual_salary_threshold => l_er_annual_salary_threshold
,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_description => p_description
,p_minimum_age => p_minimum_age
,p_ee_contribution_percent => p_ee_contribution_percent
,p_maximum_age => p_maximum_age
,p_er_contribution_percent => p_er_contribution_percent
,p_ee_annual_contribution => p_ee_annual_contribution
,p_er_annual_contribution => p_er_annual_contribution
,p_annual_premium_amount => p_annual_premium_amount
,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
,p_balance_init_element_type_id => p_balance_init_element_type_id
,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
,p_pty_attribute_category => p_pty_attribute_category
,p_pty_attribute1 => p_pty_attribute1
,p_pty_attribute2 => p_pty_attribute2
,p_pty_attribute3 => p_pty_attribute3
,p_pty_attribute4 => p_pty_attribute4
,p_pty_attribute5 => p_pty_attribute5
,p_pty_attribute6 => p_pty_attribute6
,p_pty_attribute7 => p_pty_attribute7
,p_pty_attribute8 => p_pty_attribute8
,p_pty_attribute9 => p_pty_attribute9
,p_pty_attribute10 => p_pty_attribute10
,p_pty_attribute11 => p_pty_attribute11
,p_pty_attribute12 => p_pty_attribute12
,p_pty_attribute13 => p_pty_attribute13
,p_pty_attribute14 => p_pty_attribute14
,p_pty_attribute15 => p_pty_attribute15
,p_pty_attribute16 => p_pty_attribute16
,p_pty_attribute17 => p_pty_attribute17
,p_pty_attribute18 => p_pty_attribute18
,p_pty_attribute19 => p_pty_attribute19
,p_pty_attribute20 => p_pty_attribute20
,p_pty_information_category => p_pty_information_category
,p_pty_information1 => p_pty_information1
,p_pty_information2 => p_pty_information2
,p_pty_information3 => p_pty_information3
,p_pty_information4 => p_pty_information4
,p_pty_information5 => p_pty_information5
,p_pty_information6 => p_pty_information6
,p_pty_information7 => p_pty_information7
,p_pty_information8 => p_pty_information8
,p_pty_information9 => p_pty_information9
,p_pty_information10 => p_pty_information10
,p_pty_information11 => p_pty_information11
,p_pty_information12 => p_pty_information12
,p_pty_information13 => p_pty_information13
,p_pty_information14 => p_pty_information14
,p_pty_information15 => p_pty_information15
,p_pty_information16 => p_pty_information16
,p_pty_information17 => p_pty_information17
,p_pty_information18 => p_pty_information18
,p_pty_information19 => p_pty_information19
,p_pty_information20 => p_pty_information20
,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
);
hr_utility.set_location('After Calling User Hook Update_Pension_Type_b',20);
hr_utility.set_location('Exception in User Hook Update_Pension_Type_b',25);
(p_module_name => 'Update_Pension_Type'
,p_hook_type => 'BP'
);
hr_utility.set_location(' Before Calling User Hook : Update_Pension_Type_a',20);
PQP_Pension_Types_BK2.Update_Pension_Type_a
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pension_type_id => p_pension_type_id
,p_object_version_number => l_object_version_number
,p_pension_type_name => p_pension_type_name
,p_pension_category => p_pension_category
,p_pension_provider_type => p_pension_provider_type
,p_salary_calculation_method => p_salary_calculation_method
,p_threshold_conversion_rule => p_threshold_conversion_rule
,p_contribution_conversion_rule => p_contribution_conversion_rule
,p_er_annual_limit => l_er_annual_limit
,p_ee_annual_limit => l_ee_annual_limit
,p_er_annual_salary_threshold => l_er_annual_salary_threshold
,p_ee_annual_salary_threshold => l_ee_annual_salary_threshold
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_description => p_description
,p_minimum_age => p_minimum_age
,p_ee_contribution_percent => p_ee_contribution_percent
,p_maximum_age => p_maximum_age
,p_er_contribution_percent => p_er_contribution_percent
,p_ee_annual_contribution => p_ee_annual_contribution
,p_er_annual_contribution => p_er_annual_contribution
,p_annual_premium_amount => p_annual_premium_amount
,p_ee_contribution_bal_type_id => p_ee_contribution_bal_type_id
,p_er_contribution_bal_type_id => p_er_contribution_bal_type_id
,p_balance_init_element_type_id => p_balance_init_element_type_id
,p_ee_contribution_fixed_rate => p_ee_contribution_fixed_rate --added for UK
,p_er_contribution_fixed_rate => p_er_contribution_fixed_rate --added for UK
,p_pty_attribute_category => p_pty_attribute_category
,p_pty_attribute1 => p_pty_attribute1
,p_pty_attribute2 => p_pty_attribute2
,p_pty_attribute3 => p_pty_attribute3
,p_pty_attribute4 => p_pty_attribute4
,p_pty_attribute5 => p_pty_attribute5
,p_pty_attribute6 => p_pty_attribute6
,p_pty_attribute7 => p_pty_attribute7
,p_pty_attribute8 => p_pty_attribute8
,p_pty_attribute9 => p_pty_attribute9
,p_pty_attribute10 => p_pty_attribute10
,p_pty_attribute11 => p_pty_attribute11
,p_pty_attribute12 => p_pty_attribute12
,p_pty_attribute13 => p_pty_attribute13
,p_pty_attribute14 => p_pty_attribute14
,p_pty_attribute15 => p_pty_attribute15
,p_pty_attribute16 => p_pty_attribute16
,p_pty_attribute17 => p_pty_attribute17
,p_pty_attribute18 => p_pty_attribute18
,p_pty_attribute19 => p_pty_attribute19
,p_pty_attribute20 => p_pty_attribute20
,p_pty_information_category => p_pty_information_category
,p_pty_information1 => p_pty_information1
,p_pty_information2 => p_pty_information2
,p_pty_information3 => p_pty_information3
,p_pty_information4 => p_pty_information4
,p_pty_information5 => p_pty_information5
,p_pty_information6 => p_pty_information6
,p_pty_information7 => p_pty_information7
,p_pty_information8 => p_pty_information8
,p_pty_information9 => p_pty_information9
,p_pty_information10 => p_pty_information10
,p_pty_information11 => p_pty_information11
,p_pty_information12 => p_pty_information12
,p_pty_information13 => p_pty_information13
,p_pty_information14 => p_pty_information14
,p_pty_information15 => p_pty_information15
,p_pty_information16 => p_pty_information16
,p_pty_information17 => p_pty_information17
,p_pty_information18 => p_pty_information18
,p_pty_information19 => p_pty_information19
,p_pty_information20 => p_pty_information20
,p_special_pension_type_code => p_special_pension_type_code -- added for NL Phase 2B
,p_pension_sub_category => p_pension_sub_category -- added for NL Phase 2B
,p_pension_basis_calc_method => p_pension_basis_calc_method -- added for NL Phase 2B
,p_pension_salary_balance => p_pension_salary_balance -- added for NL Phase 2B
,p_recurring_bonus_percent => p_recurring_bonus_percent -- added for NL Phase 2B
,p_non_recurring_bonus_percent => p_non_recurring_bonus_percent -- added for NL Phase 2B
,p_recurring_bonus_balance => p_recurring_bonus_balance -- added for NL Phase 2B
,p_non_recurring_bonus_balance => p_non_recurring_bonus_balance -- added for NL Phase 2B
,p_std_tax_reduction => p_std_tax_reduction -- added for NL Phase 2B
,p_spl_tax_reduction => p_spl_tax_reduction -- added for NL Phase 2B
,p_sig_sal_spl_tax_reduction => p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
,p_sig_sal_non_tax_reduction => p_sig_sal_non_tax_reduction -- added for NL Phase 2B
,p_sig_sal_std_tax_reduction => p_sig_sal_std_tax_reduction -- added for NL Phase 2B
,p_sii_std_tax_reduction => p_sii_std_tax_reduction -- added for NL Phase 2B
,p_sii_spl_tax_reduction => p_sii_spl_tax_reduction -- added for NL Phase 2B
,p_sii_non_tax_reduction => p_sii_non_tax_reduction -- added for NL Phase 2B
,p_previous_year_bonus_included => p_previous_year_bonus_included -- added for NL Phase 2B
,p_recurring_bonus_period => p_recurring_bonus_period -- added for NL Phase 2B
,p_non_recurring_bonus_period => p_non_recurring_bonus_period -- added for NL Phase 2B
,p_ee_age_threshold => p_ee_age_threshold -- added for ABP TAR fixes
,p_er_age_threshold => p_er_age_threshold -- added for ABP TAR fixes
,p_ee_age_contribution => p_ee_age_contribution -- added for ABP TAR fixes
,p_er_age_contribution => p_er_age_contribution -- added for ABP TAR fixes
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location(' After Calling User Hook : Update_Pension_Type_a',20);
hr_utility.set_location('Exception in User Hook : Update_Pension_Type_a',25);
(p_module_name => 'Update_Pension_Type'
,p_hook_type => 'AP'
);
Rollback To Update_Pension_Type;
Rollback to Update_Pension_Type;
End Update_Pension_Type;
Select ee_contribution_bal_type_id
,er_contribution_bal_type_id
,balance_init_element_type_id
From pqp_pension_types_f
Where pension_type_id = p_pension_type_id
And p_effective_date Between effective_start_date
And effective_end_date;
Procedure Delete_Balance_Init_Ele
(p_balance_init_ele_type_id in number
,p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
)
Is
--
-- Cursor to retrieve input value information
Cursor csr_get_ipv_info
Is
Select input_value_id
,object_version_number
From pay_input_values_f
Where element_type_id = p_balance_init_ele_type_id
And p_effective_date Between effective_start_date
And effective_end_date;
Select object_version_number
From pay_element_types_f
Where element_type_id = p_balance_init_ele_type_id
And p_effective_date Between effective_start_date
And effective_end_date;
l_proc varchar2(80) := g_package || 'Delete_Balance_Init_Ele';
IF p_datetrack_mode <> 'DELETE' THEN
FOR csr_get_ipv_rec IN csr_get_ipv_info
LOOP
-- Call the api to delete input values
hr_utility.set_location (l_proc, 20);
pay_input_value_api.delete_input_value
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_delete_mode => p_datetrack_mode
,p_input_value_id => csr_get_ipv_rec.input_value_id
,p_object_version_number => csr_get_ipv_rec.object_version_number
,p_effective_start_date => l_eff_start_date
,p_effective_end_date => l_eff_end_date
,p_balance_feeds_warning => l_bal_feed_warn
);
END IF; -- End if of date track mode not delete check ...
pay_element_types_api.delete_element_type
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_delete_mode => p_datetrack_mode
,p_element_type_id => p_balance_init_ele_type_id
,p_object_version_number => l_ele_ovn
,p_effective_start_date => l_eff_start_date
,p_effective_end_date => l_eff_end_date
,p_balance_feeds_warning => l_bal_feed_warn
,p_processing_rules_warning => l_prs_rule_warn
);
End Delete_Balance_Init_Ele;
Procedure Delete_EE_ER_Balances
(p_ee_contribution_bal_type_id in number
,p_er_contribution_bal_type_id in number
)
Is
--
-- Cursor to retrieve the rowid for balances
Cursor csr_get_bal_rowid (c_balance_type_id number)
Is
Select rowid
From pay_balance_types
Where balance_type_id = c_balance_type_id;
l_proc varchar2(80) := g_package || 'Delete_EE_ER_Balances';
pay_balance_types_pkg.delete_row
(x_rowid => l_rowid
,x_balance_type_id => l_bal_type_id (i)
);
End Delete_EE_ER_Balances;
SELECT rowid,
org_information_id,
organization_id,
org_information1,
nvl(org_information2,'31/12/4712') org_information2,
org_information3
FROM hr_organization_information
WHERE org_information_context = 'PQP_NL_ABP_PT'
AND org_information3 = to_char(p_pension_type_id)
AND trunc(to_date(nvl(org_information2,'31/12/4712'),'DD/MM/RRRR')) > p_effective_end_date;
hr_org_information_pkg.update_row
(x_rowid => temp_rec.rowid
,x_org_information_id => temp_rec.org_information_id
,x_org_information_context => 'PQP_NL_ABP_PT'
,x_organization_id => temp_rec.organization_id
,x_org_information1 => temp_rec.org_information1
,x_org_information2 => to_char(p_effective_end_date,'DD-MON-RR')
,x_org_information3 => temp_rec.org_information3
,x_org_information4 => null
,x_org_information5 => null
,x_org_information6 => null
,x_org_information7 => null
,x_org_information8 => null
,x_org_information9 => null
,x_org_information10 => null
,x_org_information11 => null
,x_org_information12 => null
,x_org_information13 => null
,x_org_information14 => null
,x_org_information15 => null
,x_org_information16 => null
,x_org_information17 => null
,x_org_information18 => null
,x_org_information19 => null
,x_org_information20 => null
,x_attribute_category => null
,x_attribute1 => null
,x_attribute2 => null
,x_attribute3 => null
,x_attribute4 => null
,x_attribute5 => null
,x_attribute6 => null
,x_attribute7 => null
,x_attribute8 => null
,x_attribute9 => null
,x_attribute10 => null
,x_attribute11 => null
,x_attribute12 => null
,x_attribute13 => null
,x_attribute14 => null
,x_attribute15 => null
,x_attribute16 => null
,x_attribute17 => null
,x_attribute18 => null
,x_attribute19 => null
,x_attribute20 => null
);
Procedure Delete_Pension_Type
(p_validate in Boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_pension_type_id in number
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_api_warning out nocopy varchar2
)Is
l_proc varchar2(150) := g_package||'Delete_Pension_Type';
SELECT 1
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND special_pension_type_code = 'ABP';
savepoint Delete_Pension_Type;
hr_utility.set_location('Before Calling User Hook Delete_Pension_Type_b',20);
PQP_Pension_Types_BK3.Delete_Pension_Type_b
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pension_type_id => p_pension_type_id
,p_object_version_number => l_object_version_number
);
hr_utility.set_location('After Calling User Hook Delete_Pension_Type_b',20);
hr_utility.set_location('Exception in User Hook Delete_Pension_Type_b',25);
(p_module_name => 'Delete_Pension_Type'
,p_hook_type => 'BP'
);
delete_balance_init_ele
(p_balance_init_ele_type_id => l_balance_init_ele_type_id
,p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
);
delete_ee_er_balances
(p_ee_contribution_bal_type_id => l_ee_contribution_bal_type_id
,p_er_contribution_bal_type_id => l_er_contribution_bal_type_id
);
hr_utility.set_location('Before Calling User Hook Delete_Pension_Type_a',20);
PQP_Pension_Types_BK3.Delete_Pension_Type_a
(p_validate => p_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pension_type_id => p_pension_type_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location('After Calling User Hook Delete_Pension_Type_a',20);
hr_utility.set_location('Exception in User Hook Delete_Pension_Type_a',25);
(p_module_name => 'Delete_Pension_Type'
,p_hook_type => 'AP'
);
Rollback To Delete_Pension_Type;
Rollback to Delete_Pension_Type;
End Delete_Pension_Type;