The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM pay_input_values_f_tl inptl,
pay_input_values_f inp
WHERE upper(inptl.name) = upper(p_input_name)
AND inptl.input_value_id = inp.input_value_id
AND inptl.language = p_language
AND (inp.input_value_id <> p_input_value_id OR p_input_value_id IS NULL)
AND inp.element_type_id = g_element_type_id;
select 1
from pay_link_input_values_f
where input_value_id = p_input_value_id
and default_value is null
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_effective_start_date;
select 1
from pay_element_entry_values_f ENTRY,
pay_link_input_values_f LINK
where link.input_value_id = p_input_value_id
and link.input_value_id = entry.input_value_id
and link.default_value is null
and entry.screen_entry_value is null
and entry.effective_start_date <= p_effective_end_date
and entry.effective_end_date >= p_effective_start_date
and link.effective_start_date <= p_effective_end_date
and link.effective_end_date >= p_effective_start_date;
function CANT_DELETE_ALL_INPUT_VALUES (
--
--******************************************************************************
--* Returns TRUE if any input value for a given element may not be deleted *
--******************************************************************************
--
-- Parameters are:
--
p_element_type_id number,
p_delete_mode varchar2,
p_validation_start_date date,
p_validation_end_date date,
p_error_if_true boolean default FALSE
--
) return boolean is
--
cursor csr_input_values is
select *
from pay_input_values_f
where element_type_id = p_element_type_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.cant_delete_all_input_values',1);
p_delete_mode,
p_validation_start_date,
p_validation_end_date,
p_error_if_true ) then
--
v_protected_row_exists := TRUE;
end cant_delete_all_input_values;
select 1
from dual
where exists
(select /*+ INDEX(RESULT PAY_RUN_RESULTS_PK) */ 1
from pay_run_result_values VALUE,
pay_run_results RESULT,
pay_assignment_actions ASSIGN,
pay_payroll_actions PAYROLL
where value.run_result_id = result.run_result_id
and assign.assignment_action_id = result.assignment_action_id
and assign.payroll_action_id = payroll.payroll_action_id
and value.input_value_id = p_input_value_id
and payroll.effective_date between p_validation_start_date
and p_validation_end_date);
select 1
from pay_backpay_rules
where input_value_id = p_input_value_id;
select 1
from per_absence_attendance_types
where input_value_id = p_input_value_id
and date_effective between p_validation_start_date
and p_validation_end_date;
select 1
from pay_element_entry_values_f
where input_value_id = p_input_value_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
select 1
from pay_formula_result_rules_f
where input_value_id = p_input_value_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
select 1
from per_pay_bases
where input_value_id = p_input_value_id;
select 1
from pay_element_links_f LINK,
pay_input_values_f INPUT
where input.input_value_id = p_input_value_id
and input.name = 'Pay Value'
and link.element_type_id = input.element_type_id
and link.costable_type = 'D';
select 1
from per_pay_bases BASE
where base.input_value_id = p_input_value_id;
select 1
from pay_net_calculation_rules
where input_value_id = p_input_value_id;
select 1
from pay_accrual_plans
where p_input_value_id in ( pto_input_value_id,
co_input_value_id,
residual_input_value_id );
procedure PARENT_DELETED (
--
--******************************************************************************
--* Handles the case when the element type is deleted. *
--******************************************************************************
--
-- Parameters are:
--
-- Identifier of the element
p_element_type_id number,
--
-- The effective date
p_session_date date default trunc(sysdate),
--
-- The validation period
p_validation_start_date date,
p_validation_end_date date,
--
-- The type of Date Track deletion
p_delete_mode varchar2 default 'DELETE'
--
) is
--
cursor csr_all_inputs_for_element is
select rowid,pay_input_values_f.*
from pay_input_values_f
where element_type_id = p_element_type_id
for update;
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.PARENT_DELETED',1);
p_delete_mode,
p_validation_start_date,
p_validation_end_date );
if p_delete_mode = 'ZAP'
or (p_delete_mode = 'DELETE'
and fetched_input_value.effective_start_date > p_session_date ) then
--
delete_row ( fetched_input_value.rowid,
fetched_input_value.input_value_id,
p_delete_mode,
p_session_date,
p_validation_start_date,
p_validation_end_date );
delete from hr_application_ownerships
where key_name = 'INPUT_VALUE_ID'
and key_value = fetched_input_value.input_value_id;
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_input_value.effective_start_date
and fetched_input_value.effective_end_date then
--
update pay_input_values_f
set effective_end_date = p_session_date
where current of csr_all_inputs_for_element;
elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
and p_validation_end_date = c_end_of_time then
--
-- bugfix 1507600
-- only update peices date effective as of session date
--
hr_utility.trace ('***** in DELETE_NEXT_CHANGE');
update pay_input_values_f
set effective_end_date = c_end_of_time
where --current of csr_all_inputs_for_element
rowid = fetched_input_value.rowid
and not exists
(select null
from pay_input_values_f pipv
where pipv.element_type_id = fetched_input_value.element_type_id
and pipv.input_value_id = fetched_input_value.input_value_id
and pipv.effective_start_date > fetched_input_value.effective_start_date);
end parent_deleted;
select *
from pay_input_values_f
where element_type_id = p_element_type_id
and generate_db_items_flag = 'Y'; -- Bug 6432304
hrdyndbi.delete_input_value_dict (fetched_input_value.input_value_id);
p_delete_mode varchar2,
p_validation_start_date date,
p_validation_end_date date,
p_error_if_true boolean default FALSE
) return boolean is
v_deletion_allowed boolean := TRUE;
if (p_delete_mode = 'ZAP'
and (element_entry_value_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
or accrual_plan_exists ( p_input_value_id,
p_error_if_true)
or net_calculation_rule_exists (p_input_value_id,
p_error_if_true)
or assigned_salary_base_exists (p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true)
or run_result_value_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
or result_rule_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
or absence_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
or backpay_rule_exists ( p_input_value_id,
p_error_if_true )
or distributed_cost_link_exists ( p_input_value_id,
p_error_if_true)
)
or input_value_used_as_pay_basis( p_input_value_id,
p_error_if_true))
or (p_delete_mode = 'DELETE'
and (result_rule_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
or absence_exists ( p_input_value_id,
p_validation_start_date,
p_validation_end_date,
p_error_if_true )
))
or dt_api.rows_exist(
p_base_table_name => 'ben_acty_base_rt_f',
p_base_key_column => 'input_value_id',
p_base_key_value => p_input_value_id,
p_from_date => p_validation_start_date,
p_to_date => p_validation_end_date
)
then
v_deletion_allowed := FALSE;
select count(distinct input_value_id)
from pay_input_values_f
where element_type_id = p_element_type_id;
function DATE_EFFECTIVELY_UPDATED (
--
--******************************************************************************
--* Returns TRUE if there is more than one date effective row for the input *
--* value *
--******************************************************************************
--
-- Parameters are:
--
-- Identifier of the input value and its particular instance
p_input_value_id number,
p_rowid varchar2) return boolean is
--
cursor csr_dated_updates is
select 1
from pay_input_values_f
where input_value_id = p_input_value_id
and rowid <> p_rowid;
v_date_effectively_updated boolean := FALSE;
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DATE_EFFECTIVELY_UPDATED',1);
open csr_dated_updates;
fetch csr_dated_updates into g_dummy;
v_date_effectively_updated := csr_dated_updates%found;
close csr_dated_updates;
return v_date_effectively_updated;
end date_effectively_updated;
select 1
from pay_input_values_f_tl iv_tl,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and (iv.rowid <> p_rowid or p_rowid is null)
and iv_tl.input_value_id = iv.input_value_id
and iv_tl.language = userenv('LANG')
and upper(iv_tl.name) = upper(p_name);
select input_value_id
into l_ivid
from pay_input_values_f
where rowid = p_rowid;
select 1
from pay_input_values_f
where input_value_id = p_input_value_id
and mandatory_flag = 'Y'
and effective_start_date > p_session_date;
procedure INSERT_ROW (
--
--******************************************************************************
--* Handles the insertion of an input value into the base table and ensures *
--* that any cascaded actions are carried out
--******************************************************************************
--
-- Parameters are:
--
-- All base table columns
p_effective_start_date date default trunc (sysdate),
p_effective_end_date date default to_date ('31/12/4712',
'DD/MM/YYYY'),
p_element_type_id number,
p_lookup_type varchar2 default null,
p_business_group_id number default null,
p_legislation_code varchar2 default null,
p_formula_id number default null,
p_display_sequence number default 1,
p_generate_db_items_flag varchar2 default 'Y',
p_hot_default_flag varchar2 default 'N',
p_mandatory_flag varchar2 default 'N',
-- change 115.12 - make p_name default to null
--p_name varchar2 default 'Pay Value',
p_name varchar2 default null,
-- change 115.12 - make p_base_name a mandatory parameter
--p_base_name varchar2 default 'Pay Value',
p_base_name varchar2,
p_uom varchar2 default 'M',
p_default_value varchar2 default null,
p_legislation_subgroup varchar2 default null,
p_max_value varchar2 default null,
p_min_value varchar2 default null,
p_warning_or_error varchar2 default null,
--
-- Attributes of the parent element type which will affect
-- subsequent actions
p_classification_id number default null,
--
-- Enhancement 2793978
p_value_set_id number default null,
--
-- The identifiers generated by the system for return to the form
p_input_value_id in out nocopy number,
p_rowid in out nocopy varchar2
--
) is
--
cursor csr_next_id is
select pay_input_values_s.nextval
from sys.dual;
select rowid
from pay_input_values_f
where input_value_id = p_input_value_id
and effective_start_date = p_effective_start_date
and effective_end_date = p_effective_end_date;
select L.LANGUAGE_CODE
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PAY_INPUT_VALUES_F_TL T
where T.INPUT_VALUE_ID = c_input_value_id
and T.LANGUAGE = L.LANGUAGE_CODE);
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',1);
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',2);
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',3);
insert into pay_input_values_f (
--
INPUT_VALUE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ELEMENT_TYPE_ID,
LOOKUP_TYPE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_ID,
DISPLAY_SEQUENCE,
GENERATE_DB_ITEMS_FLAG,
HOT_DEFAULT_FLAG,
MANDATORY_FLAG,
NAME,
UOM,
DEFAULT_VALUE,
LEGISLATION_SUBGROUP,
MAX_VALUE,
MIN_VALUE,
WARNING_OR_ERROR,
-- Enhancement 2793978
VALUE_SET_ID,
--
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
--
values (
--
p_input_value_id,
p_effective_start_date,
p_effective_end_date,
p_element_type_id,
p_lookup_type,
p_business_group_id,
p_legislation_code,
p_formula_id,
p_display_sequence,
p_generate_db_items_flag,
p_hot_default_flag,
p_mandatory_flag,
-- If the input value is a pay value, translate it to local language
-- DECODE(UPPER(p_name),
-- 'PAY VALUE', v_pay_value_name,
-- p_name
-- ),
-- --
-- only insert the base value into the _F table
p_base_name,
-- --
p_uom,
p_default_value,
p_legislation_subgroup,
p_max_value,
p_min_value,
p_warning_or_error,
-- Enhancement 2793978
p_value_set_id,
--
sysdate,
c_user_id,
c_login_id,
sysdate,
c_user_id);
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',4);
select meaning
into l_tl_name
from fnd_lookup_values
where lookup_type = 'NAME_TRANSLATIONS'
and lookup_code = 'PAY VALUE'
and language = c_lang_rec.language_code;
insert into PAY_INPUT_VALUES_F_TL (
INPUT_VALUE_ID,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
P_INPUT_VALUE_ID,
l_tl_name,
sysdate,
c_user_id,
c_user_id,
c_login_id,
sysdate,
c_lang_rec.language_code,
userenv('LANG')
from dual;
insert into PAY_INPUT_VALUES_F_TL (
INPUT_VALUE_ID,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
P_INPUT_VALUE_ID,
l_name,
sysdate,
c_user_id,
c_user_id,
c_login_id,
sysdate,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PAY_INPUT_VALUES_F_TL T
where T.INPUT_VALUE_ID = P_INPUT_VALUE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.insert_row',5);
insert into hr_application_ownerships
(key_name,
key_value,
product_name)
select 'INPUT_VALUE_ID',
p_input_value_id,
ao.product_name
from hr_application_ownerships ao
where ao.key_name = 'ELEMENT_TYPE_ID'
and ao.key_value = p_element_type_id
and not exists (select 'INPUT_VALUE_ID',
p_input_value_id,
ao.product_name
from hr_application_ownerships ao
where ao.key_name = 'ELEMENT_TYPE_ID'
and ao.key_value = p_element_type_id);
end insert_row;
procedure UPDATE_ROW(
--
--******************************************************************************
--* Handles the updating of the base table for the form which is based on a *
--* non-updatable view *
--******************************************************************************
--
-- Parameters are:
--
-- All base table columns
p_ROWID VARCHAR2,
p_INPUT_VALUE_ID NUMBER,
p_EFFECTIVE_START_DATE DATE,
p_EFFECTIVE_END_DATE DATE,
p_ELEMENT_TYPE_ID NUMBER,
p_LOOKUP_TYPE VARCHAR2,
p_BUSINESS_GROUP_ID NUMBER,
p_LEGISLATION_CODE VARCHAR2,
p_FORMULA_ID NUMBER,
p_DISPLAY_SEQUENCE NUMBER,
p_GENERATE_DB_ITEMS_FLAG VARCHAR2,
p_HOT_DEFAULT_FLAG VARCHAR2,
p_MANDATORY_FLAG VARCHAR2,
p_NAME VARCHAR2,
p_UOM VARCHAR2,
p_DEFAULT_VALUE VARCHAR2,
p_LEGISLATION_SUBGROUP VARCHAR2,
p_MAX_VALUE VARCHAR2,
p_MIN_VALUE VARCHAR2,
p_WARNING_OR_ERROR VARCHAR2,
-- Enhancement 2793978
p_value_set_id number default null,
--
p_recreate_db_items varchar2,
p_base_name varchar2
--
) is
--
begin
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.update_row',1);
update pay_input_values_f
set INPUT_VALUE_ID = p_INPUT_VALUE_ID,
EFFECTIVE_START_DATE = p_EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE = p_EFFECTIVE_END_DATE,
ELEMENT_TYPE_ID = p_ELEMENT_TYPE_ID,
LOOKUP_TYPE = p_LOOKUP_TYPE,
BUSINESS_GROUP_ID = p_BUSINESS_GROUP_ID,
LEGISLATION_CODE = p_LEGISLATION_CODE,
FORMULA_ID = p_FORMULA_ID,
DISPLAY_SEQUENCE = p_DISPLAY_SEQUENCE,
GENERATE_DB_ITEMS_FLAG = p_GENERATE_DB_ITEMS_FLAG,
HOT_DEFAULT_FLAG = p_HOT_DEFAULT_FLAG,
MANDATORY_FLAG = p_MANDATORY_FLAG,
-- --
NAME = p_base_NAME,
-- --
UOM = p_UOM,
DEFAULT_VALUE = p_DEFAULT_VALUE,
LEGISLATION_SUBGROUP = p_LEGISLATION_SUBGROUP,
MAX_VALUE = p_MAX_VALUE,
MIN_VALUE = p_MIN_VALUE,
WARNING_OR_ERROR = p_WARNING_OR_ERROR,
-- Enhancement 2793978
VALUE_SET_ID = p_VALUE_SET_ID,
--
last_update_date = sysdate,
last_updated_by = c_user_id,
last_update_login = c_login_id
where rowid = p_rowid;
update PAY_INPUT_VALUES_F_TL
set
NAME = P_NAME,
last_update_date = sysdate,
last_updated_by = c_user_id,
last_update_login = c_login_id,
SOURCE_LANG = userenv('LANG')
where INPUT_VALUE_ID = P_INPUT_VALUE_ID
and userenv('LANG') = LANGUAGE ; -- bug 6125295
if sql%notfound then -- trap system errors during update
hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.UPDATE_TL_ROW');
hrdyndbi.delete_input_value_dict (p_input_value_id);
end update_row;
procedure DELETE_ROW (
--
--******************************************************************************
--* Handles deletion from the base table for the form which is based on a *
--* non-updatable view, and maintains data integrity *
--******************************************************************************
--
-- Parameters are:
--
-- Identifier of the row to be deleted
p_rowid varchar2,
p_input_value_id number,
--
-- Date Track delete mode
p_delete_mode varchar2,
--
-- Validation period
p_session_date date,
p_validation_start_date date
default to_date ('01/01/0001','DD/MM/YYYY'),
p_validation_end_date date
default to_date ('31/12/4712','DD/MM/YYYY')
) is
--
begin
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',1);
p_delete_mode,
p_validation_start_date,
p_validation_end_date ) then
--
hr_balance_feeds.del_bf_input_value ( p_input_value_id,
p_delete_mode,
p_validation_start_date,
p_validation_end_date );
pay_link_input_values_pkg.parent_deleted ( p_input_value_id,
p_session_date,
p_validation_start_date,
p_validation_end_date,
p_delete_mode,
'PAY_INPUT_VALUES_F' );
hr_utility.set_location ('PAY_INPUT_VALUES_PKG.DELETE_ROW',2);
hrdyndbi.delete_input_value_dict (p_input_value_id);
delete from pay_input_values_f
where rowid = p_rowid;
hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_ROW');
if p_delete_mode = 'ZAP' then
delete from PAY_INPUT_VALUES_F_TL
where INPUT_VALUE_ID = P_INPUT_VALUE_ID;
hr_utility.set_message_token('PROCEDURE','PAY_INPUT_VALUES_PKG.DELETE_TL_ROW');
end delete_row;
select *
from pay_input_values_f
where rowid = p_rowid
for update of input_value_id nowait;
select
NAME,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from PAY_INPUT_VALUES_F_TL
where INPUT_VALUE_ID = P_INPUT_VALUE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of INPUT_VALUE_ID nowait;
delete from PAY_INPUT_VALUES_F_TL T
where not exists
(select NULL
from PAY_INPUT_VALUES_F B
where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
);
update PAY_INPUT_VALUES_F_TL T set (
NAME
) = (select
B.NAME
from PAY_INPUT_VALUES_F_TL B
where B.INPUT_VALUE_ID = T.INPUT_VALUE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.INPUT_VALUE_ID,
T.LANGUAGE
) in (select
SUBT.INPUT_VALUE_ID,
SUBT.LANGUAGE
from PAY_INPUT_VALUES_F_TL SUBB, PAY_INPUT_VALUES_F_TL SUBT
where SUBB.INPUT_VALUE_ID = SUBT.INPUT_VALUE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
));
insert into PAY_INPUT_VALUES_F_TL (
INPUT_VALUE_ID,
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.INPUT_VALUE_ID,
B.NAME,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PAY_INPUT_VALUES_F_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PAY_INPUT_VALUES_F_TL T
where T.INPUT_VALUE_ID = B.INPUT_VALUE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT count(*) INTO result
FROM pay_element_types_f E, pay_input_values_F I
WHERE I.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
and nvl(E.ELEMENT_NAME,'~null~') = nvl(X_I_E_ELEMENT_NAME,'~null~')
and nvl(E.LEGISLATION_CODE,'~null~') = nvl(X_I_E_LEGISLATION_CODE,'~null~')
and E.EFFECTIVE_START_DATE = X_I_E_EFFECTIVE_START_DATE
and E.EFFECTIVE_end_DATE = X_I_E_EFFECTIVE_END_DATE
and X_I_E_EFFECTIVE_START_DATE is not NULL
and X_I_E_EFFECTIVE_END_DATE is not NULL
and E.BUSINESS_GROUP_ID is NULL
and nvl(I.NAME,'~null~') = nvl(X_I_NAME,'~null~')
and nvl(I.LEGISLATION_CODE,'~null~') = nvl(X_I_LEGISLATION_CODE,'~null~')
and I.EFFECTIVE_START_DATE = X_I_EFFECTIVE_START_DATE
and I.EFFECTIVE_end_DATE = X_I_EFFECTIVE_END_DATE
and X_I_EFFECTIVE_START_DATE is not NULL
and X_I_EFFECTIVE_END_DATE is not NULL
and I.BUSINESS_GROUP_ID is NULL;
select i.input_value_id
from pay_element_types_f e
, pay_input_values_f i
WHERE i.element_type_id = e.element_type_id
and nvl(e.element_name,'~null~') = nvl(x_i_e_element_name,'~null~')
and nvl(e.legislation_code,'~null~') = nvl(x_i_e_legislation_code,'~null~')
and e.effective_start_date = x_i_e_effective_start_date
and e.effective_END_date = x_i_e_effective_end_date
and x_i_e_effective_start_date is not null
and x_i_e_effective_end_date is not null
and e.business_group_id is null
and nvl(i.name,'~null~') = nvl(x_i_name,'~null~')
and nvl(i.legislation_code,'~null~') = nvl(x_i_legislation_code,'~null~')
and i.effective_start_date = x_i_effective_start_date
and i.effective_end_date = x_i_effective_end_date
and x_i_effective_start_date is not null
and x_i_effective_end_date is not null
and i.business_group_id is null
;
select name
, language
from pay_input_values_f_tl
where input_value_id = p_input_value_id
and p_language in (language, source_lang)
;
UPDATE pay_input_values_f_tl
SET name = nvl(x_name,name),
last_update_date = sysdate,
last_updated_by = decode(x_owner,'SEED',1,0),
last_update_login = 0,
source_lang = userenv('LANG')
WHERE userenv('LANG') IN (language,source_lang)
AND input_value_id = l_input_value_id
;