[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
X_Accrual_Plan_Id IN OUT NUMBER,
X_Business_Group_Id NUMBER,
X_Accrual_Plan_Element_Type_Id NUMBER,
X_Pto_Input_Value_Id NUMBER,
X_Co_Input_Value_Id NUMBER,
X_Residual_Input_Value_Id NUMBER,
X_Accrual_Category VARCHAR2,
X_Accrual_Plan_Name VARCHAR2,
X_Accrual_Start VARCHAR2,
X_Accrual_Units_Of_Measure VARCHAR2,
X_Ineligible_Period_Length NUMBER,
X_Ineligible_Period_Type VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM PAY_ACCRUAL_PLANS
WHERE accrual_plan_id = X_Accrual_Plan_Id;
CURSOR C2 IS SELECT pay_accrual_plans_s.nextval FROM sys.dual;
INSERT INTO PAY_ACCRUAL_PLANS(
accrual_plan_id,
business_group_id,
accrual_plan_element_type_id,
pto_input_value_id,
co_input_value_id,
residual_input_value_id,
accrual_category,
accrual_plan_name,
accrual_start,
accrual_units_of_measure,
ineligible_period_length,
ineligible_period_type
) VALUES (
X_Accrual_Plan_Id,
X_Business_Group_Id,
X_Accrual_Plan_Element_Type_Id,
X_Pto_Input_Value_Id,
X_Co_Input_Value_Id,
X_Residual_Input_Value_Id,
X_Accrual_Category,
X_Accrual_Plan_Name,
X_Accrual_Start,
X_Accrual_Units_Of_Measure,
X_Ineligible_Period_Length,
X_Ineligible_Period_Type
);
END Insert_Row;
SELECT *
FROM PAY_ACCRUAL_PLANS
WHERE rowid = X_Rowid
FOR UPDATE of Accrual_Plan_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Accrual_Plan_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Accrual_Plan_Element_Type_Id NUMBER,
X_Pto_Input_Value_Id NUMBER,
X_Co_Input_Value_Id NUMBER,
X_Residual_Input_Value_Id NUMBER,
X_Accrual_Category VARCHAR2,
X_Accrual_Plan_Name VARCHAR2,
X_Accrual_Start VARCHAR2,
X_Accrual_Units_Of_Measure VARCHAR2,
X_Ineligible_Period_Length NUMBER,
X_Ineligible_Period_Type VARCHAR2
) IS
BEGIN
UPDATE PAY_ACCRUAL_PLANS
SET
accrual_plan_id = X_Accrual_Plan_Id,
business_group_id = X_Business_Group_Id,
accrual_plan_element_type_id = X_Accrual_Plan_Element_Type_Id,
pto_input_value_id = X_Pto_Input_Value_Id,
co_input_value_id = X_Co_Input_Value_Id,
residual_input_value_id = X_Residual_Input_Value_Id,
accrual_category = X_Accrual_Category,
accrual_plan_name = X_Accrual_Plan_Name,
accrual_start = X_Accrual_Start,
accrual_units_of_measure = X_Accrual_Units_Of_Measure,
ineligible_period_length = X_Ineligible_Period_Length,
ineligible_period_type = X_Ineligible_Period_Type
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PAY_ACCRUAL_PLANS
WHERE rowid = X_Rowid;
END Delete_Row;
select 'Y'
from PAY_ACCRUAL_PLANS
where upper(ACCRUAL_PLAN_NAME) = upper(p_plan_name)
and ((p_accrual_plan_id is null)
or
(p_accrual_plan_id is not null
and
ACCRUAL_PLAN_ID <> p_accrual_plan_id));
select 'Y'
from PAY_ELEMENT_TYPES_F
where upper(ELEMENT_NAME) in
(upper(p_plan_name),
'RESIDUAL ' || upper(p_plan_name),
'CARRIED OVER ' || upper(p_plan_name));
NAME insert_validation
DESCRIPTION performs all of the validation required at insert time
NOTES none
****************************************************************************
*/
PROCEDURE insert_validation(p_plan_name IN varchar2,
p_accrual_plan_id IN number) IS
--
BEGIN
--
PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
p_accrual_plan_id);
END insert_validation;
pre_insert_actions routine in this package.
Added p_legislation_code and p_currency_code to the param list
passed in and used them instead of the hard coded US and USD
in the calls to PAY_DB_PAY_SETUP.create_element. RMF 27-Nov-95.
****************************************************************************
*/
FUNCTION create_element(p_element_name IN varchar2,
p_element_description IN varchar2,
p_processing_type IN varchar2,
p_bg_name IN varchar2,
p_classification_name IN varchar2,
p_legislation_code IN varchar2,
p_currency_code IN varchar2,
p_post_termination_rule IN varchar2)
RETURN number IS
--
l_effective_start_date date;
pre_insert_actions routine in this package.
Added p_legislation_code to the param list passed in and used
it instead of the hard coded US in the calls below.
RMF 27-Nov-95.
****************************************************************************
*/
FUNCTION create_input_value(p_element_name IN varchar2,
p_input_value_name IN varchar2,
p_uom_code IN varchar2,
p_bg_name IN varchar2,
p_element_type_id IN number,
p_primary_classification_id IN number,
p_business_group_id IN number,
p_recurring_flag IN varchar2,
p_legislation_code IN varchar2,
p_classification_type IN varchar2)
RETURN number IS
--
l_effective_start_date date;
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 => 'Y',
p_warning_or_error => '');
NAME pre_insert_actions
DESCRIPTION handles all of the pre-insert actions for pay_accrual_plans; at
PROCEDURE pre_insert_actions(p_plan_name IN varchar2,
p_bg_name IN varchar2,
p_plan_uom IN varchar2,
p_business_group_id IN number,
p_accrual_plan_element_type_id OUT number,
p_co_input_value_id OUT number,
p_co_element_type_id OUT number,
p_residual_input_value_id OUT number,
p_residual_element_type_id OUT number) IS
--
l_element_type_id number;
select classification_name
from pay_element_classifications
where (business_group_id = p_business_group_id
or legislation_code = l_leg_code)
and parent_classification_id is null
order by decode (classification_name, 'Information', 1, 2),
nvl (non_payments_flag, 'X') desc, classification_name;
('pay_accrual_plans_pkg.pre_insert_actions',1);
-- columns returned by the following select. These are then used
-- in setting up the elements with the correct leg code and currency
-- code. RMF 27-Nov-95.
--
select name, legislation_code, currency_code
into l_bg_name, l_leg_code, l_curr_code
from per_business_groups
where business_group_id + 0 = p_business_group_id;
select hl.meaning
into l_post_termination_rule
from hr_lookups hl
where hl.lookup_type='TERMINATION_RULE'
and hl.lookup_code='F';
END pre_insert_actions;
NAME post_insert_actions
DESCRIPTION handles all of the post-insert actions for pay_accrual_plans; at
04-AUG-95 hparicha 279860 Update Pay Value mand_flag
to 'X' - ie. not enterable
by user.
NOTES none
****************************************************************************
*/
PROCEDURE post_insert_actions(p_accrual_plan_id IN number,
p_business_group_id IN number,
p_pto_input_value_id IN number,
p_co_input_value_id IN number) IS
--
v_accrual_payval_id NUMBER(9);
hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',1);
insert into pay_net_calculation_rules(
net_calculation_rule_id,
accrual_plan_id,
business_group_id,
input_value_id,
add_or_subtract)
select
pay_net_calculation_rules_s.nextval,
p_accrual_plan_id,
p_business_group_id,
p_pto_input_value_id,
-1
from dual;
hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',2);
insert into pay_net_calculation_rules(
net_calculation_rule_id,
accrual_plan_id,
business_group_id,
input_value_id,
add_or_subtract)
select
pay_net_calculation_rules_s.nextval,
p_accrual_plan_id,
p_business_group_id,
p_co_input_value_id,
1
from dual;
hr_utility.set_location('pay_accrual_plans_pkg.post_insert_actions',3);
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,
hr_lookups hrl
WHERE pap.accrual_plan_id = p_accrual_plan_id
AND pap.accrual_plan_element_type_id = piv.element_type_id
AND piv.name = hrl.meaning
AND hrl.lookup_code = 'PAY VALUE'
AND hrl.lookup_type = 'NAME_TRANSLATIONS'
);
END post_insert_actions;
NAME update_validation
DESCRIPTION performs all of the validation required at update time
NOTES none
****************************************************************************
*/
PROCEDURE update_validation(p_plan_name IN varchar2,
p_old_plan_name IN varchar2,
p_accrual_plan_id IN number) IS
--
BEGIN
--
if p_plan_name <> p_old_plan_name then
PAY_ACCRUAL_PLANS_PKG.CHK_PLAN_NAME(p_plan_name,
p_accrual_plan_id);
END update_validation;
NAME post_update_actions
DESCRIPTION performs all of the actions required after changing a plan's
details -
changes the net calculation rule if the pto element has
changed
NOTES none
****************************************************************************
*/
PROCEDURE post_update_actions(p_accrual_plan_id IN number,
p_business_group_id IN number,
p_pto_input_value_id IN number,
p_old_pto_input_value_id IN number) IS
--
BEGIN
--
if p_pto_input_value_id <> p_old_pto_input_value_id then
--
-- delete the old pto input value from the net calculation rules
--
hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',1);
delete from pay_net_calculation_rules
where input_value_id = p_old_pto_input_value_id
and accrual_plan_id = p_accrual_plan_id;
hr_utility.set_location('pay_accrual_plans_pkg.post_update_actions',2);
insert into pay_net_calculation_rules(
net_calculation_rule_id,
accrual_plan_id,
business_group_id,
input_value_id,
add_or_subtract)
select
pay_net_calculation_rules_s.nextval,
p_accrual_plan_id,
p_business_group_id,
p_pto_input_value_id,
-1
from dual
where not exists(
select 1
from pay_net_calculation_rules
where input_value_id = p_pto_input_value_id
and accrual_plan_id = p_accrual_plan_id);
END post_update_actions;
NAME pre_delete_actions
DESCRIPTION performs all of the actions required before deleting the plan -
delete all child accrual bands;
delete all child net calculation rules;
delete the element type created for the accrual plan;
delete the residual element type;
delete the carried over element type.
NOTES none
****************************************************************************
*/
PROCEDURE pre_delete_actions(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_session_date IN date) IS
--
l_effective_start_date date;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 1);
delete from pay_accrual_bands
where accrual_plan_id = p_accrual_plan_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 2);
delete from pay_net_calculation_rules
where accrual_plan_id = p_accrual_plan_id;
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 3);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 4);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',5);
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', 6);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 7);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions',8);
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', 9);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 10);
hr_utility.set_location('pay_accrual_plans_pkg.pre_delete_actions', 11);
delete from pay_element_types_f
where element_type_id = p_residual_element_type_id;
END pre_delete_actions;