Wednesday, June 25, 2014

Function to Get Element Entry Value after Payroll Run

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