[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Nageswara 24/06/2005 115.4 4346970 Added new procedure insert_rr_value
J.N. Louw 22/11/2002 115.5 2224332 Updated maintain_ipv_links
J.N. Louw 07/11/2002 115.4 2224332 Added maintain_ipv_links
insert_ipv_link
insert_ee_value
L. Kloppers 17/10/2002 115.3 Added PROCEDURE za_term_cat_update
as a dummy for initial Core HR testing
L. Kloppers 06/05/2002 115.2 2266156 Added Exception handling to
FUNCTION get_table_value
L. Kloppers 02/05/2002 115.1 2266156 Added overloaded version of
FUNCTION get_table_value
J.N. Louw 25/04/2002 115.0 2266156 New version of the package
For previous history see
pezatbme.pkh
*/
----------------------------------------------------------------------------
-- Package Global Value
----------------------------------------------------------------------------
g_leg_code varchar2(2) := 'ZA';
PROCEDURE za_term_cat_update (
p_existing_leaving_reason IN hr_lookups.lookup_code%TYPE
, p_seeded_leaving_reason IN hr_lookups.lookup_code%TYPE
)
AS
-------------------------------------------------------------------------------
BEGIN -- MAIN --
-------------------------------------------------------------------------------
hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',1);
hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',2);
hr_utility.set_location('per_za_utility_pkg.za_term_cat_update',3);
END za_term_cat_update;
select effective_date
into g_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select legislation_code
into g_Legislation_Code
from per_business_groups
where business_group_id = P_Business_Group_id;
select pur.row_low_range_or_name
into l_meaning
from pay_user_column_instances_f puci,
pay_user_columns puc ,
pay_user_rows_f pur ,
pay_user_tables put
where put.user_table_name = p_table_name
and puc.user_table_id = put.user_table_id
and pur.user_table_id = put.user_table_id
and puci.user_row_id = pur.user_row_id
and puci.user_column_id = puc.user_column_id
and puc.user_column_name = p_column
and puci.value = p_value
and l_effective_date between pur.effective_start_date
and pur.effective_end_date
and l_effective_date between puci.effective_start_date
and puci.effective_end_date
and nvl (puci.business_group_id, P_Business_Group_id)
= P_Business_Group_id
and nvl (puci.legislation_code, g_Legislation_Code)
= g_Legislation_Code;
select 'X'
from hr_leg_lookups hll
where hll.LOOKUP_TYPE = p_lookup_type
and hll.meaning = p_entry_val
and hll.enabled_flag = 'Y'
and p_effective_date between nvl(hll.start_date_active, p_effective_date)
and nvl(hll.end_date_active, p_effective_date);
select hll.meaning
from hr_leg_lookups hll
where hll.LOOKUP_TYPE = p_lookup_type
and hll.enabled_flag = 'Y'
and p_effective_date between nvl(hll.start_date_active, p_effective_date)
and nvl(hll.end_date_active, p_effective_date)
order by hll.lookup_code;
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select tab.range_or_match
, tab.user_table_id
into l_range_or_match
, l_table_id
from pay_user_tables tab
where upper(tab.user_table_name) = upper(p_table_name)
and tab.legislation_code = g_leg_code;
select CINST.value
into l_value
from pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and C.legislation_code = g_leg_code
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and R.legislation_code = g_leg_code
and decode
( TAB.user_key_units
, 'D', to_char(fnd_date.canonical_to_date(p_row_value))
, 'N', p_row_value
, 'T', upper (p_row_value)
, null
) = decode
( TAB.user_key_units
, 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name))
, 'N', R.row_low_range_or_name
, 'T', upper (R.row_low_range_or_name)
, null
)
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and CINST.legislation_code = g_leg_code;
select CINST.value
into l_value
from pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and C.legislation_code = g_leg_code
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and R.legislation_code = g_leg_code
and fnd_number.canonical_to_number (p_row_value)
between fnd_number.canonical_to_number (R.row_low_range_or_name)
and fnd_number.canonical_to_number (R.row_high_range)
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and CINST.legislation_code = g_leg_code;
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select tab.range_or_match
, tab.user_table_id
into l_range_or_match
, l_table_id
from pay_user_tables tab
where upper(tab.user_table_name) = upper(p_table_name)
and tab.legislation_code = g_leg_code;
select CINST.value
into l_value
from pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and C.legislation_code = g_leg_code
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and R.business_group_id = p_business_group_id
and decode
( TAB.user_key_units
, 'D', to_char(fnd_date.canonical_to_date(p_row_value))
, 'N', p_row_value
, 'T', upper (p_row_value)
, null
) = decode
( TAB.user_key_units
, 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name))
, 'N', R.row_low_range_or_name
, 'T', upper (R.row_low_range_or_name)
, null
)
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and CINST.business_group_id = p_business_group_id;
select CINST.value
into l_value
from pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and C.legislation_code = g_leg_code
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and R.business_group_id = p_business_group_id
and fnd_number.canonical_to_number (p_row_value)
between fnd_number.canonical_to_number (R.row_low_range_or_name)
and fnd_number.canonical_to_number (R.row_high_range)
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and CINST.business_group_id = p_business_group_id;
PROCEDURE insert_ipv_link (
p_effective_start_date IN pay_link_input_values_f.effective_start_date%TYPE
, p_effective_end_date IN pay_link_input_values_f.effective_end_date%TYPE
, p_element_link_id IN pay_link_input_values_f.element_link_id%TYPE
, p_input_value_id IN pay_link_input_values_f.input_value_id%TYPE
, p_costed_flag IN pay_link_input_values_f.costed_flag%TYPE
, p_default_value IN pay_link_input_values_f.default_value%TYPE
, p_max_value IN pay_link_input_values_f.max_value%TYPE
, p_min_value IN pay_link_input_values_f.min_value%TYPE
, p_warning_or_error IN pay_link_input_values_f.warning_or_error%TYPE
)
AS
------------
-- Variables
------------
l_link_input_pk pay_link_input_values_f.link_input_value_id%TYPE;
hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',1);
SELECT pay_link_input_values_s.nextval
INTO l_link_input_pk
FROM dual;
hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',2);
INSERT
INTO pay_link_input_values_f
( link_input_value_id
, effective_start_date
, effective_end_date
, element_link_id
, input_value_id
, costed_flag
, default_value
, max_value
, min_value
, warning_or_error
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
)
VALUES
( l_link_input_pk
, p_effective_start_date
, p_effective_end_date
, p_element_link_id
, p_input_value_id
, p_costed_flag
, p_default_value
, p_max_value
, p_min_value
, p_warning_or_error
, sysdate
, -1
, -1
, -1
, sysdate
);
hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',3);
hr_utility.set_location('per_za_utility_pkg.insert_ipv_link',4);
END insert_ipv_link;
PROCEDURE insert_ee_value (
p_effective_start_date IN pay_element_entry_values_f.effective_start_date%TYPE
, p_effective_end_date IN pay_element_entry_values_f.effective_end_date%TYPE
, p_input_value_id IN pay_element_entry_values_f.input_value_id%TYPE
, p_element_entry_id IN pay_element_entry_values_f.element_entry_id%TYPE
, p_screen_entry_value IN pay_element_entry_values_f.screen_entry_value%TYPE
)
AS
------------
-- Variables
------------
l_entry_value_pk pay_element_entry_values_f.element_entry_value_id%TYPE;
hr_utility.set_location('per_za_utility_pkg.insert_ee_value',1);
SELECT pay_element_entry_values_s.nextval
INTO l_entry_value_pk
FROM dual;
hr_utility.set_location('per_za_utility_pkg.insert_ee_value',2);
INSERT
INTO pay_element_entry_values_f
( element_entry_value_id
, effective_start_date
, effective_end_date
, input_value_id
, element_entry_id
, screen_entry_value
)
VALUES
( l_entry_value_pk
, p_effective_start_date
, p_effective_end_date
, p_input_value_id
, p_element_entry_id
, p_screen_entry_value
);
hr_utility.set_location('per_za_utility_pkg.insert_ee_value',3);
hr_utility.set_location('per_za_utility_pkg.insert_ee_value',4);
END insert_ee_value;
select min(pel.effective_start_date)
from pay_element_links_f pel
where pel.element_link_id = p_elm_lnk_id;
select max(pel.effective_end_date)
from pay_element_links_f pel
where pel.element_link_id = p_elm_lnk_id;
select
piv.effective_start_date
, piv.effective_end_date
, pel.element_link_id
, piv.input_value_id
, piv.default_value
, piv.max_value
, piv.min_value
, piv.warning_or_error
from
pay_element_links_f pel
, pay_input_values_f piv
, pay_element_types_f pet
where
pet.element_type_id = pel.element_type_id
and pet.element_type_id = piv.element_type_id
and pet.legislation_code = 'ZA'
and pet.business_group_id is null
and pel.effective_end_date between piv.effective_start_date
and piv.effective_end_date
and pel.effective_end_date between pet.effective_start_date
and pet.effective_end_date
and pel.effective_end_date =
( select max(pel2.effective_end_date)
from pay_element_links_f pel2
where pel2.element_link_id = pel.element_link_id
)
and not exists
( select
null
from
pay_link_input_values_f pli
where
pli.element_link_id = pel.element_link_id
and pli.input_value_id = piv.input_value_id
and pli.effective_start_date >=
( select min(pel2.effective_start_date)
from pay_element_links_f pel2
where pel2.element_link_id = pli.element_link_id
)
and pli.effective_end_date <=
( select max(pel2.effective_end_date)
from pay_element_links_f pel2
where pel2.element_link_id = pli.element_link_id
)
);
select pee.effective_start_date effective_start_date
, pee.effective_end_date effective_end_date
, pee.element_entry_id element_entry_id
from pay_element_entries_f pee
where pee.element_link_id = p_element_link_id;
insert_ipv_link (
p_effective_start_date => l_ipv_link_start_date
, p_effective_end_date => l_ipv_link_end_date
, p_element_link_id => v_input_value.element_link_id
, p_input_value_id => v_input_value.input_value_id
, p_costed_flag => 'N'
, p_default_value => v_input_value.default_value
, p_max_value => v_input_value.max_value
, p_min_value => v_input_value.min_value
, p_warning_or_error => v_input_value.warning_or_error
);
insert_ee_value (
p_effective_start_date => v_entry.effective_start_date
, p_effective_end_date => v_entry.effective_end_date
, p_input_value_id => v_input_value.input_value_id
, p_element_entry_id => v_entry.element_entry_id
, p_screen_entry_value => NULL
);
SELECT context_id
FROM ff_contexts
WHERE context_name = p_context_name;
PROCEDURE insert_rr_value (
p_input_value_id IN pay_input_values_f.input_value_id%TYPE
,p_run_result_id IN pay_run_results.run_result_id%TYPE
,p_result_value IN pay_run_result_values.result_value%TYPE
)
AS
------------
-- Variable
------------
rec_exists number;
select prr.ASSIGNMENT_ACTION_ID
,prr.ELEMENT_ENTRY_ID
,peef.ASSIGNMENT_ID
from pay_run_results prr
,pay_element_entries_f peef
where prr.element_entry_id = peef.element_entry_id
and prr.run_result_id = p_run_result_id;
hr_utility.set_location('per_za_utility_pkg.insert_rr_value',1);
select pivf.name
into l_input_value_name
from pay_input_values_f pivf
where pivf.INPUT_VALUE_ID = p_input_value_id
and rownum = 1;
hr_utility.set_location('per_za_utility_pkg.insert_rr_value',2);
insert into pay_run_result_values (
INPUT_VALUE_ID
,RUN_RESULT_ID
,RESULT_VALUE)
(select
p_input_value_id
,p_run_result_id
,p_result_value
from dual
where not exists ( select null
from pay_run_result_values
where INPUT_VALUE_ID = p_input_value_id
and run_result_id = p_run_result_id
)
);
INSERT INTO pay_action_contexts
(assignment_action_id
,assignment_id
,context_id
,context_value)
(select cur_run_res_con_rec.assignment_action_id
,cur_run_res_con_rec.assignment_id
,l_Dir_no_con
,l_dir_no
from dual
where not exists (select null
from pay_action_contexts
where assignment_action_id = cur_run_res_con_rec.assignment_action_id
and assignment_id = cur_run_res_con_rec.assignment_id
and context_id = l_Dir_no_con
and context_value = l_dir_no )
);
INSERT INTO pay_action_contexts
(assignment_action_id
,assignment_id
,context_id
,context_value)
(select cur_run_res_con_rec.assignment_action_id
,cur_run_res_con_rec.assignment_id
,l_clar_no_con
,l_clar_no
from dual
where not exists (select null
from pay_action_contexts
where assignment_action_id = cur_run_res_con_rec.assignment_action_id
and assignment_id = cur_run_res_con_rec.assignment_id
and context_id = l_clar_no_con
and context_value = l_clar_no )
);
hr_utility.set_location('per_za_utility_pkg.insert_rr_value',3);
hr_utility.set_location('per_za_utility_pkg.insert_rr_value',4);
END insert_rr_value;