Saturday, January 24, 2015

Query to fetch element details for all employees in Oracle HRMS

  SELECT ppf.employee_number,
         ppf.full_name,
         ppf.person_id,
         petf.element_name,
         peef.element_entry_id,
         petf.element_type_id,
         pivf.input_value_id,
         peevf.screen_entry_value
    FROM PER_PEOPLE_F ppf,
         per_all_assignments_f paaf,
         pay_element_entries_f peef,
         PAY_ELEMENT_TYPES_F petf,
         pay_input_values_f pivf,
         pay_element_entry_values_f peevf
   WHERE     paaf.person_id = ppf.person_id
         AND paaf.assignment_id = peef.assignment_id
         AND peef.element_type_id = petf.element_type_id
         AND pivf.name IN ('Multiplier', 'Pay Value')
         AND peef.element_type_id = pivf.element_type_id
         AND pivf.input_value_id = peevf.input_value_id
         AND peef.element_entry_id = peevf.element_entry_id
         AND TO_DATE (
                NVL (TO_CHAR (ppf.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (ppf.effective_end_date, 'DD-MON-YYYY'), SYSDATE)) >=
                SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (paaf.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (paaf.effective_end_date, 'DD-MON-YYYY'), SYSDATE)) >=
                SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (peef.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (peef.effective_end_date, 'DD-MON-YYYY'), SYSDATE)) >=
                SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (petf.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (petf.effective_end_date, 'DD-MON-YYYY'), SYSDATE)) >=
                SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (peevf.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (peevf.effective_end_date, 'DD-MON-YYYY'),
                     SYSDATE)) >= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (pivf.effective_start_date, 'DD-MON-YYYY'),
                     SYSDATE)) <= SYSDATE
         AND TO_DATE (
                NVL (TO_CHAR (pivf.effective_end_date, 'DD-MON-YYYY'), SYSDATE)) >=
                SYSDATE

ORDER BY ppf.person_id;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect