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;