select
papf.person_number empnumber,
ppnf.full_name empname,
petf.base_element_name salaryelement,
pivf.base_name inputvalue,
peevf.screen_entry_value entryvalue,
to_char(peevf.effective_start_date, 'DD-MON-YYYY') elementstartdate,
to_char(peevf.effective_end_date, 'DD-MON-YYYY') elementenddate
from
pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_element_types_f petf,
pay_input_values_f pivf,
per_all_people_f papf,
per_all_assignments_f paaf,
per_person_names_f ppnf
where
1 = 1
and peef.element_entry_id = peevf.element_entry_id
and peef.element_type_id = petf.element_type_id
and peevf.element_entry_id = peef.element_entry_id
and pivf.input_value_id = peevf.input_value_id
and papf.person_id = peef.person_id
and papf.person_id = ppnf.person_id
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.assignment_type = 'E'
and ppnf.name_type = 'GLOBAL'
and pivf.element_type_id = petf.element_type_id
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date) --and petf.base_element_name like ('Basic%')
order by
petf.base_element_name,
papf.person_number asc
papf.person_number empnumber,
ppnf.full_name empname,
petf.base_element_name salaryelement,
pivf.base_name inputvalue,
peevf.screen_entry_value entryvalue,
to_char(peevf.effective_start_date, 'DD-MON-YYYY') elementstartdate,
to_char(peevf.effective_end_date, 'DD-MON-YYYY') elementenddate
from
pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_element_types_f petf,
pay_input_values_f pivf,
per_all_people_f papf,
per_all_assignments_f paaf,
per_person_names_f ppnf
where
1 = 1
and peef.element_entry_id = peevf.element_entry_id
and peef.element_type_id = petf.element_type_id
and peevf.element_entry_id = peef.element_entry_id
and pivf.input_value_id = peevf.input_value_id
and papf.person_id = peef.person_id
and papf.person_id = ppnf.person_id
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.assignment_type = 'E'
and ppnf.name_type = 'GLOBAL'
and pivf.element_type_id = petf.element_type_id
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date) --and petf.base_element_name like ('Basic%')
order by
petf.base_element_name,
papf.person_number asc