[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select least(effective_start_date),
greatest(effective_end_date)
into l_effective_start_date,
l_effective_end_date
from pay_element_types_f
where element_type_id = p_element_type_id;
p_insert_update_flag => 'INSERT',
p_input_value_id => l_input_value_id,
p_rowid => '',
p_recurring_flag => p_recurring_flag,
p_mandatory_flag => 'N',
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => p_classification_type,
p_name => p_input_value_name,
p_uom => p_uom_code,
p_min_value => '',
p_max_value => '',
p_default_value => '',
p_lookup_type => '',
p_formula_id => '',
p_generate_db_items_flag => l_generate_db_item_flag,
p_warning_or_error => '');
pay_element_links_pkg.insert_row (
p_rowid => l_dummy1,
p_element_link_id => l_dummy2,
p_effective_start_date => p_absence_link_rec.effective_start_date,
p_effective_end_date => l_max_end_date,
p_payroll_id => p_absence_link_rec.payroll_id,
p_job_id => p_absence_link_rec.job_id,
p_position_id => p_absence_link_rec.position_id,
p_people_group_id => p_absence_link_rec.people_group_id,
p_cost_allocation_keyflex_id => p_absence_link_rec.cost_allocation_keyflex_id,
p_organization_id => p_absence_link_rec.organization_id,
p_element_type_id => p_element_type_id,
p_location_id => p_absence_link_rec.location_id,
p_grade_id => p_absence_link_rec.grade_id,
p_balancing_keyflex_id => p_absence_link_rec.balancing_keyflex_id,
p_business_group_id => p_absence_link_rec.business_group_id,
p_legislation_code => p_legislation_code,
p_element_set_id => p_absence_link_rec.element_set_id,
p_pay_basis_id => p_absence_link_rec.pay_basis_id,
p_costable_type => p_absence_link_rec.costable_type,
p_link_to_all_payrolls_flag => p_absence_link_rec.link_to_all_payrolls_flag,
p_multiply_value_flag => p_absence_link_rec.multiply_value_flag,
p_standard_link_flag => p_absence_link_rec.standard_link_flag,
p_transfer_to_gl_flag => p_absence_link_rec.transfer_to_gl_flag,
p_comment_id => p_absence_link_rec.comment_id,
p_employment_category => p_absence_link_rec.employment_category,
p_qualifying_age => p_absence_link_rec.qualifying_age,
p_qualifying_length_of_service => p_absence_link_rec.qualifying_length_of_service,
p_qualifying_units => p_absence_link_rec.qualifying_units,
p_attribute_category => p_absence_link_rec.attribute_category,
p_attribute1 => p_absence_link_rec.attribute1,
p_attribute2 => p_absence_link_rec.attribute2,
p_attribute3 => p_absence_link_rec.attribute3,
p_attribute4 => p_absence_link_rec.attribute4,
p_attribute5 => p_absence_link_rec.attribute5,
p_attribute6 => p_absence_link_rec.attribute6,
p_attribute7 => p_absence_link_rec.attribute7,
p_attribute8 => p_absence_link_rec.attribute8,
p_attribute9 => p_absence_link_rec.attribute9,
p_attribute10 => p_absence_link_rec.attribute10,
p_attribute11 => p_absence_link_rec.attribute11,
p_attribute12 => p_absence_link_rec.attribute12,
p_attribute13 => p_absence_link_rec.attribute13,
p_attribute14 => p_absence_link_rec.attribute14,
p_attribute15 => p_absence_link_rec.attribute15,
p_attribute16 => p_absence_link_rec.attribute16,
p_attribute17 => p_absence_link_rec.attribute17,
p_attribute18 => p_absence_link_rec.attribute18,
p_attribute19 => p_absence_link_rec.attribute19,
p_attribute20 => p_absence_link_rec.attribute20
);
l_update_date date;
select distinct dbi.user_name
from ff_database_items dbi,
ff_user_entities ue,
pay_balance_dimensions pbd,
pay_defined_balances dfb,
pay_balance_types pbt
where dfb.balance_dimension_id = pbd.balance_dimension_id
and pbd.route_id = ue.route_id
and ue.user_entity_id = dbi.user_entity_id
and pbt.balance_type_id = dfb.balance_type_id
and dfb.defined_balance_id = p_defined_balance_id
and dbi.user_name = upper(replace(pbt.balance_name, ' ', '_')||
pbd.database_item_suffix);
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'PTO Balance Type';
select rule_mode
from pay_legislation_rules
where rule_type = 'PTO_BALANCE_TYPE'
and legislation_code = l_leg_code;
l_update_date := sysdate;
select formula_type_id
into l_formula_type_id
from ff_formula_types
where formula_type_name = 'Oracle Payroll';
ff_formulas_f_pkg.insert_row(
X_Rowid => l_rowid,
X_Formula_Id => l_formula_id,
X_Effective_Start_Date => p_effective_start_date,
X_Effective_End_Date => p_effective_end_date,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => p_legislation_code,
X_Formula_Type_Id => l_formula_type_id,
X_Formula_Name => l_formula_name,
X_Description => null,
X_Formula_Text => null,
X_Sticky_Flag => 'N',
X_Last_Update_Date => l_update_date
);
select legislation_code
into l_leg_code
from per_business_groups
where business_group_id = p_business_group_id;
update ff_formulas_f
set formula_text = l_formula_text
where formula_id = l_formula_id;
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id;
SELECT NULL
FROM pay_balance_dimensions pbd
WHERE pbd.balance_dimension_id = p_balance_dimension_id
AND NVL(pbd.business_group_id, p_business_group_id) = p_business_group_id
AND NVL(pbd.legislation_code, NVL(l_legislation_code, hr_api.g_varchar2))
= NVL(l_legislation_code, hr_api.g_varchar2)
AND EXISTS (SELECT NULL
FROM ff_routes fr
,ff_contexts fc
,ff_route_context_usages frcu
WHERE fr.route_id = pbd.route_id
AND fc.context_name IN ('ASSIGNMENT_ACTION_ID'
,'DATE_EARNED'
,'TAX_UNIT_ID')
AND frcu.route_id = fr.route_id
AND frcu.context_id = fc.context_id)
AND NOT EXISTS (SELECT NULL
FROM ff_routes fr
,ff_contexts fc
,ff_route_context_usages frcu
WHERE fr.route_id = pbd.route_id
AND fc.context_name NOT IN ('ASSIGNMENT_ACTION_ID'
,'DATE_EARNED'
,'TAX_UNIT_ID')
AND frcu.route_id = fr.route_id
AND frcu.context_id = fc.context_id)
UNION ALL
SELECT NULL
FROM pay_balance_dimensions pbd
WHERE pbd.balance_dimension_id = p_balance_dimension_id
AND NVL(pbd.business_group_id, p_business_group_id) = p_business_group_id
AND NVL(pbd.legislation_code, NVL(l_legislation_code, hr_api.g_varchar2))
= NVL(l_legislation_code, hr_api.g_varchar2)
AND EXISTS (SELECT NULL
FROM ff_routes fr
,ff_contexts fc
,ff_route_context_usages frcu
WHERE fr.route_id = pbd.route_id
AND fc.context_name = 'JURISDICTION_CODE'
AND frcu.route_id = fr.route_id
AND frcu.context_id = fc.context_id);
select classification_name,
classification_id
from pay_element_classifications
where (business_group_id = p_business_group_id or
legislation_code = p_legislation_code)
and parent_classification_id is null
order by decode (classification_name, 'Information', 1, 2),
nvl (non_payments_flag, 'X') desc,
classification_name;
select classification_id
from pay_element_classifications
where classification_name = p_classification_name
and (business_group_id = p_business_group_id or
legislation_code = p_legislation_code);
select meaning
from hr_lookups
where lookup_type = 'NAME_TRANSLATIONS'
and lookup_code = p_lookup_code;
select *
from pay_element_links_f
where element_link_id in ( select pel.element_link_id
from pay_element_links_f pel,
pay_input_values_f piv
where pel.element_type_id = piv.element_type_id
and piv.input_value_id = p_pto_input_value_id
and p_effective_date between pel.effective_start_date
and pel.effective_end_date
and p_effective_date between piv.effective_start_date
and piv.effective_end_date );
select ff.formula_id
from ff_formulas_f ff,
ff_formula_types ft
where ff.formula_type_id = ft.formula_type_id
and ft.formula_type_name = 'Element Skip'
and ff.formula_name = p_formula_name
and p_effective_date between ff.effective_start_date
and ff.effective_end_date;
select balance_category_id
from pay_balance_categories_f
where category_name = 'PTO Accruals'
and legislation_code = p_leg_code;
select balance_category_id
from pay_balance_categories_f
where category_name = 'PTO Accruals'
and legislation_code is null;
select name, legislation_code, currency_code
into l_bg_name, l_leg_code, l_curr_code
from per_business_groups
where business_group_id = p_business_group_id;
select ec.default_priority + 1
into l_priority
from pay_element_classifications ec
where upper(ec.classification_name) = upper(l_classification_name)
and ec.parent_classification_id is NULL
and ((ec.legislation_code = l_leg_code) or
(ec.legislation_code is null and
ec.business_group_id is not null and
ec.business_group_id = p_business_group_id
and not exists (select ''
from pay_element_classifications ec2
where upper(ec2.classification_name) = upper(l_classification_name)
and ec2.parent_classification_id is NULL
and ec2.legislation_code = l_leg_code)
));
select hl.meaning
into l_post_termination_rule
from hr_lookups hl
where hl.lookup_type='TERMINATION_RULE'
and hl.lookup_code='F'; -- Final Close
select processing_priority
into l_priority
from pay_element_types_f
where element_type_id = l_accrual_plan_element_type_id;
pay_balance_types_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Balance_Type_Id => l_balance_type_id,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => l_leg_code,
X_Currency_Code => null,
X_Assignment_Remuneration_Flag => 'N',
X_Balance_Name => l_balance_name,
X_Base_Balance_Name => l_balance_name,
X_Balance_Uom => l_uom_code,
X_Comments => null,
X_Legislation_Subgroup => null,
X_Reporting_Name => substr(l_balance_name, 1, 30),
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_category_id
);
pay_balance_feeds_f_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Balance_Feed_Id => l_balance_feed_id,
X_Effective_Start_Date => hr_general.start_of_time,
X_Effective_End_Date => hr_general.end_of_time,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => l_leg_code,
X_Balance_Type_Id => l_balance_type_id,
X_Input_Value_Id => l_balance_input_value_id,
X_Scale => 1,
X_Legislation_Subgroup => null
);
pay_defined_balances_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Defined_Balance_Id => l_defined_balance_id,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => l_leg_code,
X_Balance_Type_Id => l_balance_type_id,
X_Balance_Dimension_Id => p_balance_dimension_id,
X_Force_Latest_Balance_Flag=> 'N',
X_Legislation_Subgroup => null
);
pay_status_rules_pkg.insert_row (
X_Rowid => l_rowid,
X_Status_Processing_Rule_Id => l_status_processing_rule_id,
X_Effective_Start_Date => l_effective_start_date,
X_Effective_End_Date => l_effective_end_date,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => l_leg_code,
X_Element_Type_Id => l_accrual_plan_element_type_id,
X_Assignment_Status_Type_Id => null,
X_Formula_Id => l_payroll_formula_id,
X_Processing_Rule => 'P',
X_Comment_Id => null,
X_Legislation_Subgroup => null,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => l_user,
X_Last_Update_Login => l_user,
X_Created_By => l_user,
X_Creation_Date => sysdate
);
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => p_business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_balance_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'TOTAL_ACCRUED_PTO',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_balance_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => p_business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_tagging_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'DUMMY',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_tagging_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
select formula_id
into l_tagging_formula_id
from ff_formulas_f ff,
ff_formula_types ft
where ff.formula_type_id = ft.formula_type_id
and ft.formula_type_name = 'Oracle Payroll'
and ff.formula_name = 'PTO_TAGGING_FORMULA';
pay_status_rules_pkg.insert_row (
X_Rowid => l_rowid,
X_Status_Processing_Rule_Id => l_status_processing_rule_id,
X_Effective_Start_Date => l_effective_start_date,
X_Effective_End_Date => l_effective_end_date,
X_Business_Group_Id => p_business_group_id,
X_Legislation_Code => l_leg_code,
X_Element_Type_Id => l_tagging_element_type_id,
X_Assignment_Status_Type_Id => null,
X_Formula_Id => l_tagging_formula_id,
X_Processing_Rule => 'P',
X_Comment_Id => null,
X_Legislation_Subgroup => null,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => l_user,
X_Last_Update_Login => l_user,
X_Created_By => l_user,
X_Creation_Date => sysdate
);
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => p_business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_tagging_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'RETRO_ELEMENT_ENTRY_ID',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_tagging_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
UPDATE pay_input_values_f
SET mandatory_flag = 'X'
WHERE input_value_id =
( SELECT piv.input_value_id
FROM pay_input_values_f piv,
pay_accrual_plans pap
WHERE pap.accrual_plan_id = p_accrual_plan_id
AND pap.accrual_plan_element_type_id = piv.element_type_id
AND piv.name = 'Pay Value'
);
PROCEDURE update_accrual_plan
(p_validate in boolean default false
,p_effective_date in date
,p_accrual_plan_id in number
,p_pto_input_value_id in number default hr_api.g_number
,p_accrual_category in varchar2 default hr_api.g_varchar2
,p_accrual_start in varchar2 default hr_api.g_varchar2
,p_ineligible_period_length in number default hr_api.g_number
,p_ineligible_period_type in varchar2 default hr_api.g_varchar2
,p_accrual_formula_id in number default hr_api.g_number
,p_co_formula_id in number default hr_api.g_number
,p_description in varchar2 default hr_api.g_varchar2
,p_ineligibility_formula_id in number default hr_api.g_number
,p_balance_dimension_id in number default hr_api.g_number
,p_information_category in varchar2 default hr_api.g_varchar2
,p_information1 in varchar2 default hr_api.g_varchar2
,p_information2 in varchar2 default hr_api.g_varchar2
,p_information3 in varchar2 default hr_api.g_varchar2
,p_information4 in varchar2 default hr_api.g_varchar2
,p_information5 in varchar2 default hr_api.g_varchar2
,p_information6 in varchar2 default hr_api.g_varchar2
,p_information7 in varchar2 default hr_api.g_varchar2
,p_information8 in varchar2 default hr_api.g_varchar2
,p_information9 in varchar2 default hr_api.g_varchar2
,p_information10 in varchar2 default hr_api.g_varchar2
,p_information11 in varchar2 default hr_api.g_varchar2
,p_information12 in varchar2 default hr_api.g_varchar2
,p_information13 in varchar2 default hr_api.g_varchar2
,p_information14 in varchar2 default hr_api.g_varchar2
,p_information15 in varchar2 default hr_api.g_varchar2
,p_information16 in varchar2 default hr_api.g_varchar2
,p_information17 in varchar2 default hr_api.g_varchar2
,p_information18 in varchar2 default hr_api.g_varchar2
,p_information19 in varchar2 default hr_api.g_varchar2
,p_information20 in varchar2 default hr_api.g_varchar2
,p_information21 in varchar2 default hr_api.g_varchar2
,p_information22 in varchar2 default hr_api.g_varchar2
,p_information23 in varchar2 default hr_api.g_varchar2
,p_information24 in varchar2 default hr_api.g_varchar2
,p_information25 in varchar2 default hr_api.g_varchar2
,p_information26 in varchar2 default hr_api.g_varchar2
,p_information27 in varchar2 default hr_api.g_varchar2
,p_information28 in varchar2 default hr_api.g_varchar2
,p_information29 in varchar2 default hr_api.g_varchar2
,p_information30 in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_payroll_formula_id out nocopy number
,p_defined_balance_id out nocopy number
,p_balance_element_type_id out nocopy number
,p_tagging_element_type_id out nocopy number
,p_check_accrual_ff out nocopy boolean)
IS
--
-- Fetches the current accrual plan.
--
cursor csr_get_accrual_plan IS
select *
from pay_accrual_plans pap
where pap.accrual_plan_id = p_accrual_plan_id;
select net_calculation_rule_id,
object_version_number
from pay_net_calculation_rules
where accrual_plan_id = p_accrual_plan_id
and input_value_id = p_pto_input_value_id;
select classification_id
from pay_element_classifications
where classification_name = p_classification_name
and (business_group_id = p_business_group_id or
legislation_code = p_legislation_code);
select *
from pay_element_links_f
where element_link_id in ( select distinct pel.element_link_id
from pay_element_links_f pel,
pay_input_values_f piv
where pel.element_type_id = piv.element_type_id
and piv.input_value_id = p_pto_input_value_id);
select meaning
from hr_lookups
where lookup_type = 'NAME_TRANSLATIONS'
and lookup_code = p_lookup_code;
select balance_category_id
from pay_balance_categories_f
where category_name = 'PTO Accruals'
and legislation_code = p_leg_code;
select balance_category_id
from pay_balance_categories_f
where category_name = 'PTO Accruals'
and legislation_code is null;
l_proc := g_package||'update_accrual_plan';
savepoint update_accrual_plan;
hr_accrual_plan_bk2.update_accrual_plan_b
(p_accrual_plan_id => p_accrual_plan_id
,p_pto_input_value_id => p_pto_input_value_id
,p_accrual_category => p_accrual_category
,p_accrual_start => p_accrual_start
,p_ineligible_period_length => p_ineligible_period_length
,p_ineligible_period_type => p_ineligible_period_type
,p_accrual_formula_id => p_accrual_formula_id
,p_co_formula_id => p_co_formula_id
,p_description => p_description
,p_ineligibility_formula_id => p_ineligibility_formula_id
,p_balance_dimension_id => p_balance_dimension_id
,p_object_version_number => p_object_version_number
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
,p_information21 => p_information21
,p_information22 => p_information22
,p_information23 => p_information23
,p_information24 => p_information24
,p_information25 => p_information25
,p_information26 => p_information26
,p_information27 => p_information27
,p_information28 => p_information28
,p_information29 => p_information29
,p_information30 => p_information30
);
(p_module_name => 'update_accrual_plan'
,p_hook_type => 'BP'
);
select name, legislation_code, currency_code
into l_bg_name, l_leg_code, l_curr_code
from per_business_groups
where business_group_id = l_plan_rec.business_group_id;
select processing_priority + 1
into l_priority
from pay_element_types_f
where element_type_id = l_plan_rec.accrual_plan_element_type_id;
select hl.meaning
into l_post_termination_rule
from hr_lookups hl
where hl.lookup_type='TERMINATION_RULE'
and hl.lookup_code='F'; -- Final Close
select processing_priority
into l_priority
from pay_element_types_f
where element_type_id = l_plan_rec.accrual_plan_element_type_id;
pay_balance_types_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Balance_Type_Id => l_balance_type_id,
X_Business_Group_Id => l_plan_rec.business_group_id,
X_Legislation_Code => l_leg_code,
X_Currency_Code => null,
X_Assignment_Remuneration_Flag => 'N',
X_Balance_Name => l_balance_name,
X_Base_Balance_Name => l_balance_name,
X_Balance_Uom => l_uom_code,
X_Comments => null,
X_Legislation_Subgroup => null,
X_Reporting_Name => substr(l_balance_name, 1, 30),
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_category_id
);
select input_value_id
into l_balance_input_value_id
from pay_input_values_f
where element_type_id = l_plan_rec.balance_element_type_id
and uom = l_uom_code;
pay_balance_feeds_f_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Balance_Feed_Id => l_balance_feed_id,
X_Effective_Start_Date => hr_general.start_of_time,
X_Effective_End_Date => hr_general.end_of_time,
X_Business_Group_Id => l_plan_rec.business_group_id,
X_Legislation_Code => l_leg_code,
X_Balance_Type_Id => l_balance_type_id,
X_Input_Value_Id => l_balance_input_value_id,
X_Scale => 1,
X_Legislation_Subgroup => null
);
pay_defined_balances_pkg.Insert_Row(
X_Rowid => l_rowid,
X_Defined_Balance_Id => l_plan_rec.defined_balance_id,
X_Business_Group_Id => l_plan_rec.business_group_id,
X_Legislation_Code => l_leg_code,
X_Balance_Type_Id => l_balance_type_id,
X_Balance_Dimension_Id => p_balance_dimension_id,
X_Force_Latest_Balance_Flag=> 'N',
X_Legislation_Subgroup => null
);
pay_status_rules_pkg.insert_row
(X_Rowid => l_rowid,
X_Status_Processing_Rule_Id => l_status_processing_rule_id,
X_Effective_Start_Date => l_effective_start_date,
X_Effective_End_Date => l_effective_end_date,
X_Business_Group_Id => l_plan_rec.business_group_id,
X_Legislation_Code => l_leg_code,
X_Element_Type_Id => l_plan_rec.accrual_plan_element_type_id,
X_Assignment_Status_Type_Id => null,
X_Formula_Id => l_plan_rec.payroll_formula_id,
X_Processing_Rule => 'P',
X_Comment_Id => null,
X_Legislation_Subgroup => null,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => l_user,
X_Last_Update_Login => l_user,
X_Created_By => l_user,
X_Creation_Date => sysdate
);
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => l_plan_rec.business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_plan_rec.balance_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'TOTAL_ACCRUED_PTO',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_balance_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
select input_value_id
into l_tagging_input_value_id
from pay_input_values_f
where element_type_id = l_plan_rec.tagging_element_type_id
and uom = 'N';
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => l_plan_rec.business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'DUMMY',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_tagging_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
select formula_id
into l_tagging_formula_id
from ff_formulas_f ff,
ff_formula_types ft
where ff.formula_type_id = ft.formula_type_id
and ft.formula_type_name = 'Oracle Payroll'
and ff.formula_name = 'PTO_TAGGING_FORMULA';
pay_status_rules_pkg.insert_row
(X_Rowid => l_rowid,
X_Status_Processing_Rule_Id => l_status_processing_rule_id,
X_Effective_Start_Date => l_effective_start_date,
X_Effective_End_Date => l_effective_end_date,
X_Business_Group_Id => l_plan_rec.business_group_id,
X_Legislation_Code => l_leg_code,
X_Element_Type_Id => l_plan_rec.tagging_element_type_id,
X_Assignment_Status_Type_Id => null,
X_Formula_Id => l_tagging_formula_id,
X_Processing_Rule => 'P',
X_Comment_Id => null,
X_Legislation_Subgroup => null,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => l_user,
X_Last_Update_Login => l_user,
X_Created_By => l_user,
X_Creation_Date => sysdate
);
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 => l_effective_start_date,
p_Effective_End_Date => l_effective_end_date,
p_Business_Group_Id => l_plan_rec.business_group_id,
p_Legislation_Code => l_leg_code,
p_Element_Type_Id => l_plan_rec.tagging_element_type_id,
p_Status_Processing_Rule_Id => l_status_processing_rule_id,
p_Result_Name => 'RETRO_ELEMENT_ENTRY_ID',
p_Result_Rule_Type => 'I',
p_Legislation_Subgroup => null,
p_Severity_Level => 'I',
p_Input_Value_Id => l_tagging_input_value_id,
p_Created_By => l_user,
p_session_date => sysdate
);
pay_ncr_api.delete_pay_net_calc_rule
(p_net_calculation_rule_id => l_ncr_rec.net_calculation_rule_id,
p_object_version_number => l_ncr_rec.object_version_number
);
hr_accrual_plan_bk2.update_accrual_plan_a
(p_accrual_plan_id => p_accrual_plan_id
,p_pto_input_value_id => p_pto_input_value_id
,p_accrual_category => p_accrual_category
,p_accrual_start => p_accrual_start
,p_ineligible_period_length => p_ineligible_period_length
,p_ineligible_period_type => p_ineligible_period_type
,p_accrual_formula_id => p_accrual_formula_id
,p_co_formula_id => p_co_formula_id
,p_description => p_description
,p_ineligibility_formula_id => p_ineligibility_formula_id
,p_payroll_formula_id => p_payroll_formula_id
,p_defined_balance_id => l_plan_rec.defined_balance_id
,p_balance_dimension_id => p_balance_dimension_id
,p_tagging_element_type_id => l_plan_rec.tagging_element_type_id
,p_balance_element_type_id => l_plan_rec.balance_element_type_id
,p_object_version_number => l_object_version_number
,p_information_category => p_information_category
,p_information1 => p_information1
,p_information2 => p_information2
,p_information3 => p_information3
,p_information4 => p_information4
,p_information5 => p_information5
,p_information6 => p_information6
,p_information7 => p_information7
,p_information8 => p_information8
,p_information9 => p_information9
,p_information10 => p_information10
,p_information11 => p_information11
,p_information12 => p_information12
,p_information13 => p_information13
,p_information14 => p_information14
,p_information15 => p_information15
,p_information16 => p_information16
,p_information17 => p_information17
,p_information18 => p_information18
,p_information19 => p_information19
,p_information20 => p_information20
,p_information21 => p_information21
,p_information22 => p_information22
,p_information23 => p_information23
,p_information24 => p_information24
,p_information25 => p_information25
,p_information26 => p_information26
,p_information27 => p_information27
,p_information28 => p_information28
,p_information29 => p_information29
,p_information30 => p_information30
);
(p_module_name => 'update_accrual_plan'
,p_hook_type => 'AP'
);
ROLLBACK TO update_accrual_plan;
ROLLBACK to update_accrual_plan;
END update_accrual_plan;
procedure delete_accrual_plan
(p_validate in boolean default false
,p_effective_date in date
,p_accrual_plan_id in number
,p_accrual_plan_element_type_id in number
,p_co_element_type_id in number
,p_residual_element_type_id in number
,p_balance_element_type_id in number
,p_tagging_element_type_id in number
,p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
cursor c_get_ncr(p_accrual_plan_id number) is
select net_calculation_rule_id,
object_version_number
from pay_net_calculation_rules
where accrual_plan_id = p_accrual_plan_id;
l_proc := g_package||'delete_accrual_plan';
savepoint delete_accrual_plan;
hr_accrual_plan_bk3.delete_accrual_plan_b
(p_effective_date => p_effective_date
,p_accrual_plan_id => p_accrual_plan_id
,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
,p_co_element_type_id => p_co_element_type_id
,p_residual_element_type_id => p_residual_element_type_id
,p_balance_element_type_id => p_balance_element_type_id
,p_tagging_element_type_id => p_tagging_element_type_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_accrual_plan'
,p_hook_type => 'BP'
);
delete from pay_accrual_bands
where accrual_plan_id = p_accrual_plan_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 41);
pay_ncr_api.delete_pay_net_calc_rule (
p_net_calculation_rule_id => l_ncr.net_calculation_rule_id,
p_object_version_number => l_ncr.object_version_number
);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 42);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 43);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',44);
delete from pay_element_types_f
where element_type_id = p_accrual_plan_element_type_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 45);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 46);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',47);
hr_utility.trace('DELETE ELEMENT: '||to_char(p_co_element_type_id));
delete from pay_element_types_f
where element_type_id = p_co_element_type_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 48);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 49);
delete from pay_element_types_f
where element_type_id = p_residual_element_type_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
delete from pay_element_types_f
where element_type_id = p_balance_element_type_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 50);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 51);
delete from pay_element_types_f
where element_type_id = p_tagging_element_type_id;
hr_accrual_plan_bk3.delete_accrual_plan_a
(p_effective_date => p_effective_date
,p_accrual_plan_id => p_accrual_plan_id
,p_accrual_plan_element_type_id => p_accrual_plan_element_type_id
,p_co_element_type_id => p_co_element_type_id
,p_residual_element_type_id => p_residual_element_type_id
,p_balance_element_type_id => p_balance_element_type_id
,p_tagging_element_type_id => p_tagging_element_type_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_accrual_plan'
,p_hook_type => 'AP'
);
rollback to delete_accrual_plan;
rollback to delete_accrual_plan;
end delete_accrual_plan;