Hrms Technical Queries - (Elements & Values Assigned to the Employee)

***This query will display the elements assigned to the employee in assignment entries.

SELECT ppf.employee_number, ppf.full_name,
       TO_CHAR (ppf.effective_start_date, 'DD-MON-YYYY') emp_start_date,
       TO_CHAR (ppf.effective_end_date, 'DD-MON-YYYY') emp_end_date,
       pet.element_name,
       TO_CHAR (pee.effective_start_date,
                'DD-MON-YYYY'
               ) element_entry_start_date,
       TO_CHAR (pee.effective_end_date, 'DD-MON-YYYY') element_entry_end_date
  FROM per_all_people_f ppf,
       per_all_assignments_f pam,
       pay_element_entries_f pee,
       pay_element_types_f pet
 WHERE 1 = 1
--   AND ppf.employee_number = '1000063'
   AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND ppf.person_id = pam.person_id
   AND SYSDATE BETWEEN pam.effective_start_date AND pam.effective_end_date
   AND pam.assignment_id = pee.assignment_id
   AND pee.element_type_id = pet.element_type_id
   AND SYSDATE BETWEEN pet.effective_start_date AND pet.effective_end_date
   and ppf.employee_number not like '%X'

   order by ppf.employee_number


***This query will display the values assigned to the elements in assignment entries.

SELECT   ppf.employee_number, ppf.effective_start_date emp_start_date,
         ppf.office_number sector, pet.element_name, pi.NAME input_value,
         pef.screen_entry_value
    FROM xx_employee_master_stg x,
         per_all_people_f ppf,
         per_all_assignments_f pam,
         pay_element_entries_f pee,
         pay_element_types_f pet,
         pay_input_values_f pi,
         pay_element_entry_values_f pef
   WHERE 1 = 1
     --AND ppf.employee_number = '1033097'
     AND x.creation_date = '15-DEC-19'
     AND x.employee_number = ppf.employee_number
     AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND ppf.person_id = pam.person_id
     AND SYSDATE BETWEEN pam.effective_start_date AND pam.effective_end_date
     AND pam.assignment_id = pee.assignment_id
     AND pee.element_type_id = pet.element_type_id
     AND pet.element_type_id = pi.element_type_id
     AND pi.NAME IN ('Monthly Allowance', 'Monthly Amount')
     AND pee.element_entry_id = pef.element_entry_id
     AND pi.input_value_id = pef.input_value_id

ORDER BY ppf.employee_number