[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Checks attributes of inserted and update input values for concurrence
with business rules.
*/
--
PROCEDURE chk_input_value(p_element_type_id in number,
p_legislation_code in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_insert_update_flag in varchar2,
p_input_value_id in number,
p_rowid in varchar2,
p_recurring_flag in varchar2,
p_mandatory_flag in varchar2,
p_hot_default_flag in varchar2,
p_standard_link_flag in varchar2,
p_classification_type in varchar2,
p_name in varchar2,
p_uom in varchar2,
p_min_value in varchar2,
p_max_value in varchar2,
p_default_value in varchar2,
p_lookup_type in varchar2,
p_formula_id in number,
p_generate_db_items_flag in varchar2,
p_warning_or_error in varchar2) is
--
v_validation_check varchar2(1);
if p_insert_update_flag = 'INSERT' then
-- Make sure that a maximum of 6 input values can be created
begin
--
select count(distinct iv.input_value_id)
into v_num_input_values
from pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and p_val_start_date between
iv.effective_start_date and iv.effective_end_date;
select 'N'
into v_validation_check
from sys.dual
where exists
(select 1
from pay_element_links_f el,
pay_element_entries_f ee
where p_element_type_id = el.element_type_id
and el.element_link_id = ee.element_link_id
and ee.effective_end_date >= p_val_start_date
and ee.effective_start_date <= p_val_end_date);
end if;-- In INSERT mode
select 'N'
into v_validation_check
from sys.dual
where exists
(select 1
from pay_input_values_f_tl ipv_tl,
pay_input_values_f ipv
where ipv_tl.input_value_id = ipv.input_value_id
and userenv('LANG') = ipv_tl.language
and ipv.element_type_id = p_element_type_id
and ipv.input_value_id <> p_input_value_id
and upper(p_name) = upper(ipv_tl.name));
select 'N'
into v_validation_check
from sys.dual
where not exists(select 1
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_default_value);
select 'N'
into v_validation_check
from sys.dual
where exists
(select 1
from pay_run_results rr
where rr.element_type_id = p_element_type_id);
if any nulls are found in the selected entries. It will also return TRUE if
there are no entries at all for this link and input value. This allows for
the fact that entries may be created subsequently with null values.
*/
--
FUNCTION chk_entry_default(f_input_value_id in number,
f_element_link_id in number,
f_val_start_date in date,
f_val_end_date in date) return BOOLEAN is
--
null_entries_found varchar2(1) := 'N';
select 'Y'
into null_entries_found
from sys.dual
where not exists(
select 1
from pay_element_entries_f ee,
pay_element_entry_values_f eev
where f_element_link_id = ee.element_link_id
and ee.element_entry_id = eev.element_entry_id
and eev.input_value_id = f_input_value_id
and eev.effective_start_date <= f_val_end_date
and eev.effective_end_date >= f_val_start_date);
select 'Y'
into null_entries_found
from sys.dual
where exists(
select 1
from pay_element_entries_f ee,
pay_element_entry_values_f eev
where f_element_link_id = ee.element_link_id
and ee.element_entry_id = eev.element_entry_id
and eev.input_value_id = f_input_value_id
and eev.effective_start_date <= f_val_end_date
and eev.effective_end_date >= f_val_start_date
and eev.screen_entry_value is null);
PROCEDURE chk_link_hot_defaults(p_update_mode in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_input_value_id in number,
p_element_link_id in number,
p_default_delete in varchar2,
p_min_delete in varchar2,
p_max_delete in varchar2) is
--
--
null_links_found varchar2(1);
if (p_min_delete = 'Y') then
--
begin
--
select 'Y'
into l_min_value_missing
from sys.dual
where exists
(select 1
from pay_input_values_f iv
where p_input_value_id = iv.input_value_id
and iv.min_value is null
and iv.effective_start_date <= p_val_end_date
and iv.effective_end_date >= p_val_start_date);
if (p_max_delete = 'Y') then
--
begin
--
select 'Y'
into l_max_value_missing
from sys.dual
where exists
(select 1
from pay_input_values_f iv
where p_input_value_id = iv.input_value_id
and iv.max_value is null
and iv.effective_start_date <= p_val_end_date
and iv.effective_end_date >= p_val_start_date);
if (p_default_delete = 'Y') then
--
begin
--
select 'Y'
into l_default_value_missing
from sys.dual
where exists
(select 1
from pay_input_values_f iv
where p_input_value_id = iv.input_value_id
and iv.default_value is null
and iv.effective_start_date <= p_val_end_date
and iv.effective_end_date >= p_val_start_date);
PROCEDURE chk_hot_defaults(p_update_mode in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_input_value_id in number,
p_element_type_id in number,
p_default_deleted in varchar2,
p_min_deleted in varchar2,
p_max_deleted in varchar2) is
--
null_links_found varchar2(1);
select element_link_id,
effective_start_date,
effective_end_date
from pay_link_input_values_f
where input_value_id = p_input_value_id
and default_value is null
and effective_end_date >= p_val_start_date
and effective_start_date <= p_val_end_date;
if p_default_deleted = 'Y' then
--
-- Go though all the links checking they have defaults.
-- If any don't then check the element entry value exists.
-- the function 'chk_entry_default' will return 'TRUE' if any entries
-- are found without values entered for them.
--
for chk_default in c_chk_link_default( p_val_start_date,
p_val_end_date,
p_input_value_id) loop
if hr_input_values.chk_entry_default(p_input_value_id,
chk_default.element_link_id,
chk_default.effective_start_date,
chk_default.effective_end_date) then
hr_utility.set_message(801,'PAY_6191_INPVAL_NO_ENTRY_DEFS');
if p_min_deleted = 'Y' then
--
-- Check that there are no link input values over the validation period
-- that have a null minimum default.
begin
--
select 'Y'
into null_links_found
from pay_link_input_values_f
where input_value_id = p_input_value_id
and min_value is null
and effective_end_date >= p_val_start_date
and effective_start_date <= p_val_end_date;
if p_max_deleted = 'Y' then
--
-- Check that there are no link input values over the validation period
-- that have a null maximum default.
begin
--
select 'Y'
into null_links_found
from pay_link_input_values_f
where input_value_id = p_input_value_id
and max_value is null
and effective_end_date >= p_val_start_date
and effective_start_date <= p_val_end_date;
Checks whether an input value can be deleted. This consists of checking
if various child records exist for this input value.
*/
--
PROCEDURE chk_del_input_values(p_delete_mode in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_input_value_id in number) is
--
v_links_exist_flag varchar2(1) := 'N';
if p_delete_mode = 'FUTURE_CHANGE' then
hr_utility.set_message(801,'PAY_6209_ELEMENT_NO_FC_DEL');
elsif p_delete_mode = 'ZAP' then
--
begin
-- if 'ZAP' then
-- test to see if there are any element links during validation period.
-- and input value is PAY VALUE and the link is distributed
--
l_pay_value_name := hr_input_values.get_pay_value_name(null);
select 'Y'
into v_links_exist_flag
from sys.dual
where exists
(select 1
from pay_element_links_f el,
pay_input_values_f_tl ip_tl,
pay_input_values_f ip
where ip_tl.input_value_id = ip.input_value_id
and ip.input_value_id = p_input_value_id
and userenv('LANG') = ip_tl.language
and ip_tl.name = l_pay_value_name
and el.element_type_id = ip.element_type_id
and el.costable_type = 'D'
and el.effective_start_date <= p_val_end_date
and el.effective_end_date >= p_val_start_date);
if p_delete_mode = 'DELETE' or p_delete_mode = 'ZAP' then
--
begin
-- Test to see if there are any element entry values during validation period
--
select 'Y'
into v_entries_exist_flag
from sys.dual
where exists
(select 1
from pay_element_entry_values_f
where input_value_id = p_input_value_id
and effective_start_date <= p_val_end_date
and effective_end_date >= p_val_start_date);
select 'Y'
into v_results_exist_flag
from sys.dual
where exists
(select 1
from pay_formula_result_rules_f
where input_value_id = p_input_value_id
and effective_start_date <= p_val_end_date
and effective_end_date >= p_val_start_date);
select 'Y'
into v_run_results_exist_flag
from sys.dual
where exists
(select /*+ INDEX(rr PAY_RUN_RESULTS_PK) */ 1
from pay_run_result_values rrv,
pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where p_input_value_id = rrv.input_value_id
and rrv.run_result_id = rr.run_result_id
and aa.assignment_action_id = rr.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date between
p_val_start_date and p_val_end_date);
select 'Y'
into v_results_exist_flag
from sys.dual
where exists
(select 1
from per_absence_attendance_types
where input_value_id = p_input_value_id
and date_effective between
p_val_start_date and p_val_end_date);
select 'Y'
into v_results_exist_flag
from sys.dual
where exists
(select 1
from pay_backpay_rules
where input_value_id = p_input_value_id);
end if; -- of check delete mode condition.
chk_field_update
DESCRIPTION
A general function for input values that forces correction for a particular
field over the lifetime of a complete input value. It should be called after
the postfield datetrack trigger.
*/
FUNCTION chk_field_update(
p_input_value_id in number,
p_val_start_date in date,
p_val_end_date in date,
p_update_mode in varchar2) return BOOLEAN is
--
l_validation_check varchar2(1) := 'N';
if (p_update_mode <> 'CORRECTION') then
--
return FALSE;
select 'Y'
into l_validation_check
from sys.dual
where p_val_end_date =
(select max(iv1.effective_end_date)
from pay_input_values iv1
where iv1.input_value_id = p_input_value_id)
and p_val_start_date =
(select min(iv2.effective_start_date)
from pay_input_values iv2
where iv2.input_value_id = p_input_value_id);
end chk_field_update;
select meaning
into l_pay_value_name
from hr_lookups
where lookup_type = 'NAME_TRANSLATIONS'
and lookup_code = 'PAY VALUE';
Checks whether an input value can be updated. Some values can be updated
under any circumstances and others can only be updated if certain conditions
exist. For instance if there are no links in existence. This procedure calls
chk_hot_defaults.
*/
--
PROCEDURE chk_upd_input_values(p_update_mode in varchar2,
p_val_start_date in date,
p_val_end_date in date,
p_classification_type in varchar2,
p_old_name in varchar2,
p_name in varchar2,
p_input_value_id in number,
p_element_type_id in number,
p_old_uom in varchar2,
p_uom in varchar2,
p_old_db_items_flag in varchar2,
p_db_items_flag in varchar2,
p_old_default_value in varchar2,
p_default_value in varchar2,
p_old_min_value in varchar2,
p_min_value in varchar2,
p_old_max_value in varchar2,
p_max_value in varchar2,
p_old_error_flag in varchar2,
p_error_flag in varchar2,
p_old_mandatory_flag in varchar2,
p_mandatory_flag in varchar2,
p_old_formula_id in number,
p_formula_id in number,
p_old_lookup_type in varchar2,
p_lookup_type in varchar2,
p_business_group_id in number,
p_legislation_code in varchar2) is
--
local_warning exception;
((p_update_mode <> 'CORRECTION') or
(p_old_name = l_pay_value_name) or
(p_name = l_pay_value_name) or
(p_business_group_id is null)) then
hr_utility.set_message(801,'PAY_6177_INPVAL_NO_NAME_CHANGE');
select 'Y'
into l_validation_check
from sys.dual
where exists
(select 1
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id
and iv.effective_start_date > p_val_start_date
and iv.mandatory_flag = 'Y');
select 'Y'
into v_entries_exist_flag
from sys.dual
where exists
(select 1
from pay_element_links_f el,
pay_element_entries_f ee
where p_element_type_id = el.element_type_id
and el.element_link_id = ee.element_link_id
and el.effective_start_date <= p_val_end_date
and el.effective_end_date >= p_val_start_date
and ee.effective_start_date <= p_val_end_date
and ee.effective_end_date >= p_val_start_date);
if chk_field_update(p_input_value_id,
p_val_start_date,
p_val_end_date,
p_update_mode) = FALSE then
--
if (p_old_name <> p_name) then
--
hr_utility.set_message(801,'PAY_6632_INPVAL_NO_NAME_UPD');
select 'Y'
into l_link_inputs_exist
from sys.dual
where exists
(select 1
from pay_link_input_values_f liv
where liv.input_value_id = p_input_value_id);
This behaviour is controlled by the p_insert_type parameter which can take
the values 'INSERT_LINK' or 'INSERT_INPUT_VALUE'.
*/
--
PROCEDURE
create_link_input_value(p_insert_type varchar2,
p_element_link_id number,
p_input_value_id number,
p_input_value_name varchar2,
p_costable_type varchar2,
p_validation_start_date date,
p_validation_end_date date,
p_default_value varchar2,
p_max_value varchar2,
p_min_value varchar2,
p_warning_or_error_flag varchar2,
p_hot_default_flag varchar2,
p_legislation_code varchar2,
p_pay_value_name varchar2,
p_element_type_id number) is
--
v_link_input_value_id number;
select iv.input_value_id input_value_id
from pay_input_values_f iv
where iv.element_type_id = p_element_type_id
order by iv.input_value_id
for update;
if p_insert_type = 'INSERT_LINK' then
--
-- For each input value for the element type NB. this locks all the records
for iv_rec in c_input_value(p_element_type_id) loop
--
-- Check to see if this input value has already been processed. If it has
-- then do not process again
if iv_rec.input_value_id <> v_old_input_value_id then
--
-- Get sequence number for link_input_value
select pay_link_input_values_s.nextval
into v_link_input_value_id
from sys.dual;
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)
select
v_link_input_value_id,
greatest(p_validation_start_date,iv.effective_start_date),
least(p_validation_end_date,iv.effective_end_date),
p_element_link_id,
iv.input_value_id,
decode(p_costable_type,
'F', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
'C', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
'D', decode(iv_tl.name, p_pay_value_name, 'Y','N'),
'N'),
decode(iv.hot_default_flag,'Y',NULL,iv.default_value),
decode(iv.hot_default_flag,'Y',NULL,iv.max_value),
decode(iv.hot_default_flag,'Y',NULL,iv.min_value),
decode(iv.hot_default_flag,'Y',NULL,iv.warning_or_error),
sysdate,
-1,
-1,
-1,
sysdate
from pay_input_values_f_tl iv_tl,
pay_input_values_f iv
where iv_tl.input_value_id = iv.input_value_id
and iv.input_value_id = iv_rec.input_value_id
and userenv('LANG') = iv_tl.language
and iv.effective_start_date <= p_validation_end_date
and iv.effective_end_date >= p_validation_start_date;
elsif p_insert_type = 'INSERT_INPUT_VALUE' then
--
-- insert link input values when an new input value has been inserted and
-- links already exist.
--
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)
select
pay_link_input_values_s.nextval,
greatest(p_validation_start_date,eL.effective_start_date),
least(p_validation_end_date,eL.effective_end_date),
el.element_link_id,
p_input_value_id,
decode(el.costable_type,
'F', decode(P_input_value_name, p_pay_value_name, 'Y','N'),
'C', decode(p_input_value_name, p_pay_value_name, 'Y','N'),
'D', decode(p_input_value_name, p_pay_value_name, 'Y','N'),
'N'),
decode(p_hot_default_flag,'Y',NULL,p_default_value),
decode(p_hot_default_flag,'Y',NULL,p_max_value),
decode(P_hot_default_flag,'Y',NULL,p_min_value),
decode(p_hot_default_flag,'Y',NULL,p_warning_or_error_flag),
sysdate,
-1,
-1,
-1,
sysdate
from pay_element_links_F el
where p_element_type_id = el.element_type_id
and el.effective_start_date <= p_validation_end_date
and el.effective_end_date >= p_validation_start_date;
End if; -- decision code for insert type.
This procedure controls the third party inserts when an input value is
created manually. (Rather than being created at the same time as an element
type.) It calls the procedures create_link_input_value and
hr_balances.ins_balance_feed.
*/
--
PROCEDURE ins_3p_input_values(p_val_start_date in date,
p_val_end_date in date,
p_element_type_id in number,
p_primary_classification_id in number,
p_input_value_id in number,
p_default_value in varchar2,
p_max_value in varchar2,
p_min_value in varchar2,
p_warning_or_error_flag in varchar2,
p_input_value_name in varchar2,
p_db_items_flag in varchar2,
p_costable_type in varchar2,
p_hot_default_flag in varchar2,
p_business_group_id in number,
p_legislation_code in varchar2,
p_startup_mode in varchar2) is
--
l_pay_value_name varchar2(80);
hr_input_values.create_link_input_value('INSERT_INPUT_VALUE',
NULL,
p_input_value_id ,
p_input_value_name ,
NULL,
p_val_start_date ,
p_val_end_date ,
p_default_value ,
p_max_value ,
p_min_value ,
p_warning_or_error_flag ,
p_hot_default_flag ,
p_legislation_code ,
l_pay_value_name ,
p_element_type_id );
This procedure should be called on post delete. When the name has been
updated and create database items is set to Yes then the database items
will be dropped and recreated. This will fail if it is unable to drop the
database items.
*/
PROCEDURE upd_3p_input_values(p_input_value_id in number,
p_val_start_date in date,
p_old_name in varchar2,
p_name in varchar2,
p_db_items_flag in varchar2,
p_old_db_items_flag in varchar2) is
--
begin
--
if (p_db_items_flag = 'Y') and (p_old_name <> p_name) then
--
hrdyndbi.delete_input_value_dict(
p_input_value_id);
hrdyndbi.delete_input_value_dict(
p_input_value_id);
This procedure does the necessary cascade deletes when deleting an input
value. This only deletes balance feeds. It calls the procedure -
hr.balances.del_balance_feed.
*/
--
PROCEDURE del_3p_input_values(p_delete_mode in varchar2,
p_input_value_id in number,
p_db_items_flag in varchar2,
p_val_end_date in date,
p_session_date in date,
p_startup_mode in varchar2) is
--
l_delete_mode varchar2(30);
p_delete_mode,
NULL,
p_input_value_id,
NULL,
NULL,
NULL,
NULL,
NULL,
p_session_date,
p_val_end_date,
NULL,
p_startup_mode);
if p_delete_mode = 'ZAP' then
delete
from pay_link_input_values_f
where input_value_id = p_input_value_id;
elsif p_delete_mode = 'DELETE_NEXT_CHANGE' then
--
-- DELETE_NEXT_CHANGE will only affect the link input value records if we are
-- on The final record of the input value. In this case the final link input
-- value records will need to be extended to the end of time.
--
begin
--
select 'Y'
into l_on_final_record
from pay_input_values_f iv1
where p_input_value_id = iv1.input_value_id
and p_session_date between
iv1.effective_start_date and iv1.effective_end_date
and iv1.effective_end_date =
(select max(iv2.effective_end_date)
from pay_input_values_f iv2
where p_input_value_id = iv2.input_value_id);
update pay_link_input_values_f lv1
set lv1.effective_end_date = v_end_of_time
where p_input_value_id = lv1.input_value_id
and lv1.effective_end_date =
(select max(lv2.effective_end_date)
from pay_link_input_values_f lv2
where lv2.link_input_value_id = lv1.link_input_value_id
and lv2.input_value_id = p_input_value_id);
hrdyndbi.delete_input_value_dict(p_input_value_id);