CREATE OR REPLACE FUNCTION xxh_get_element_entry_val_f (p_person_id NUMBER,
p_effective_date DATE,
p_entry_type VARCHAR2,
p_element_name VARCHAR2)
RETURN VARCHAR2
AS
l_result_value VARCHAR2
(1000) := NULL;
BEGIN
SELECT prrv.result_value
INTO l_result_value
FROM pay_payroll_actions ppa,
pay_assignment_actions pac,
per_all_assignments_f paaf,
per_all_people_f per,
pay_run_results prr,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prrv,
per_time_periods_v ptp
WHERE paaf.assignment_type = 'E'
AND paaf.assignment_status_type_id = 1
AND paaf.primary_flag = 'Y'
AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date
AND ptp.start_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND ptp.start_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND ptp.start_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND ptp.start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.payroll_action_id = pac.payroll_action_id
AND pac.assignment_id = paaf.assignment_id
AND paaf.person_id = per.person_id
AND pac.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = prrv.input_value_id
AND ppa.time_period_id = ptp.time_period_id
AND UPPER (piv.NAME) = UPPER (p_entry_type)
AND UPPER (pet.element_name) = UPPER (p_element_name)
AND paaf.person_id = p_person_id;
RETURN l_result_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END xxh_get_element_entry_val_f;
--------------------------------------------------------------------------------------------------------------------
select xxh_get_element_entry_val_f(107818,to_date('01-MAY-2014'),'PAY VALUE','SOCIAL') from dual
--------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment