[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
values select tuned to use
PAY_ELEMENT_ENTRY_VALUES_F_N50 index.
25-Feb-99 J. Moyano 115.1 MLS changes. Procedure affected:
get_input_value_details.
16-Sep-04 T.Habara 115.2 Added original_entry_name().
============================================================================*/
--
-----------------------------------------------------------------------------
-- Name --
-- original_entry_name --
-- Purpose --
-- This function is used for displaying the original entry name for --
-- the specified element entry id. --
-- --
-- Arguments --
-- See below. --
-- Notes --
-- None. --
-----------------------------------------------------------------------------
--
FUNCTION original_entry_name
(p_original_entry_id in number
) return varchar2
is
l_element_name pay_element_types_f_tl.element_name%type;
select
pettl.element_name
from
pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f_tl pettl
where
pee.element_entry_id = p_original_entry_id
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pettl.element_type_id = pel.element_type_id
and pettl.language = userenv('lang')
;
SELECT COUNT(*)
INTO p_number_of_input_values
FROM PAY_INPUT_VALUES_F IV
WHERE IV.ELEMENT_TYPE_ID = p_element_type_id
AND p_session_date
BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE;
v_select_input_value_id number;
SELECT HIV1.INPUT_VALUE_ID,
HIV1_TL.NAME,
HIV1.MANDATORY_FLAG,
HIV1.UOM,
DECODE(HIV1.HOT_DEFAULT_FLAG,'Y',
NVL(HLIV1.WARNING_OR_ERROR,HIV1.WARNING_OR_ERROR),
HIV1.WARNING_OR_ERROR),
HIV1.HOT_DEFAULT_FLAG,
HIV1.LOOKUP_TYPE,
HIV1.FORMULA_ID
INTO v_select_input_value_id,
p_input_name,
p_mandatory_flag,
v_uom,
p_warning_or_error,
v_hot_default_flag,
v_lookup_type,
p_formula_id
FROM PAY_INPUT_VALUES_F_TL HIV1_TL,
PAY_INPUT_VALUES_F HIV1,
PAY_LINK_INPUT_VALUES_F HLIV1
WHERE HIV1_TL.INPUT_VALUE_ID = HIV1.INPUT_VALUE_ID
and userenv('LANG') = HIV1_TL.language
AND p_session_date
BETWEEN HLIV1.EFFECTIVE_START_DATE
AND HLIV1.EFFECTIVE_END_DATE
AND HLIV1.INPUT_VALUE_ID = HIV1.INPUT_VALUE_ID
AND HLIV1.ELEMENT_LINK_ID = p_element_link_id
AND HIV1.ROWID =
(SELECT MIN(IV1.ROWID)
FROM PAY_INPUT_VALUES_F IV1
WHERE IV1.ELEMENT_TYPE_ID = p_element_type_id
AND p_session_date
BETWEEN IV1.EFFECTIVE_START_DATE
AND IV1.EFFECTIVE_END_DATE
AND IV1.INPUT_VALUE_ID NOT IN
(NVL(p_input_value_id1,0),
NVL(p_input_value_id2,0),
NVL(p_input_value_id3,0),
NVL(p_input_value_id4,0),
NVL(p_input_value_id5,0),
NVL(p_input_value_id6,0))
AND IV1.DISPLAY_SEQUENCE =
(SELECT MIN(IV2.DISPLAY_SEQUENCE)
FROM PAY_INPUT_VALUES_F IV2
WHERE IV2.ELEMENT_TYPE_ID = p_element_type_id
AND p_session_date
BETWEEN IV2.EFFECTIVE_START_DATE
AND IV2.EFFECTIVE_END_DATE
AND IV2.INPUT_VALUE_ID NOT IN
(NVL(p_input_value_id1,0),
NVL(p_input_value_id2,0),
NVL(p_input_value_id3,0),
NVL(p_input_value_id4,0),
NVL(p_input_value_id5,0),
NVL(p_input_value_id6,0))));
p_input_value_id := v_select_input_value_id;
SELECT HEEV1.SCREEN_ENTRY_VALUE
INTO v_database_format_value
FROM PAY_ELEMENT_ENTRY_VALUES_F HEEV1
WHERE HEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
AND HEEV1.INPUT_VALUE_ID + 0 = v_select_input_value_id
AND p_session_date
BETWEEN HEEV1.EFFECTIVE_START_DATE AND HEEV1.EFFECTIVE_END_DATE;
v_select_input_value_id,
p_session_date,
p_input_currency_code,
'DEF',
v_screen_format_value,
v_database_format_value);
select h.meaning
into v_screen_format_value
from hr_lookups h
where h.lookup_type = v_lookup_type
and h.lookup_code = v_database_format_value;
v_select_input_value_id,
p_session_date,
p_input_currency_code,
'DEF',
v_screen_format_value,
v_database_format_value);