Function to Get Input Value ID from pay_element_entry_values_f

 

 
-- +=========================================================================================+

-- | Name : element_input_value_id

-- | Description : This function finds element input value ID

-- | Parameters : p_element_name, p_input_name, p_business_group_id ,p_assignment_id ,p_dated |

-- +==========================================================================================+


FUNCTION element_input_value_id (

p_element_name IN
VARCHAR2,

p_input_name IN
VARCHAR2,

p_business_group_id IN
NUMBER,

p_assignment_id IN
NUMBER,

p_dated IN
DATE


)


RETURN
NUMBER


IS


CURSOR cur_element_input_value

IS


SELECT pev.input_value_id

FROM pay_element_entries_f pef,

pay_element_entry_values_f pev,

pay_element_links_f pel,

pay_element_types_f pet,

pay_input_values_f piv

WHERE pet.element_name = p_element_name

AND piv.NAME
= p_input_name

AND pet.business_group_id = p_business_group_id

AND pef.assignment_id = p_assignment_id

AND pet.business_group_id = pel.business_group_id

AND pet.business_group_id = piv.business_group_id

AND pef.element_entry_id = pev.element_entry_id

AND pef.element_link_id = pel.element_link_id

AND pel.element_type_id = pet.element_type_id

AND pet.element_type_id = piv.element_type_id

AND pev.input_value_id = piv.input_value_id

AND p_dated BETWEEN pet.effective_start_date

AND pet.effective_end_date

AND p_dated BETWEEN pel.effective_start_date

AND pel.effective_end_date

AND p_dated BETWEEN pef.effective_start_date

AND pef.effective_end_date

AND p_dated BETWEEN pev.effective_start_date

AND pev.effective_end_date

AND p_dated BETWEEN piv.effective_start_date

AND piv.effective_end_date;



ln_input_value_id pay_input_values_f.input_value_id%TYPE;


BEGIN


OPEN cur_element_input_value;




FETCH cur_element_input_value

INTO ln_input_value_id;




CLOSE cur_element_input_value;




RETURN ln_input_value_id;


EXCEPTION


WHEN
OTHERS


THEN


RETURN
NULL;


END;