Employee Entries and Element entries

select petf.element_name "element entry name",

  paaf.person_id,

  peef.assignment_id,

  papf.full_name "employee name",

  pivf.name "input fields",

  peevf.effective_start_date "entry start date",

  peevf.effective_end_date "entry end date",

  peevf.screen_entry_value

from pay_element_types_f petf,

  pay_element_links_f pelf,

  pay_element_entries_f peef,

  per_all_assignments_f paaf,

  per_all_people_f papf,

  pay_element_entry_values_f peevf, --- version 2.08 changes ---

  pay_input_values_f pivf

where 1 = 1

--and petf.element_name    = 'life cnty pd' --or 'emb accrual'

and pelf.element_type_id = petf.element_type_id

--and pivf.name            ='ee contr'

and peef.element_type_id = petf.element_type_id

and trunc (sysdate) between trunc (peef.effective_start_date) and trunc (peef.effective_end_date)

and trunc (sysdate) between trunc (petf.effective_start_date) and trunc (petf.effective_end_date)

and trunc (sysdate) between trunc (pelf.effective_start_date) and trunc (pelf.effective_end_date)

and paaf.assignment_id = peef.assignment_id

and trunc (sysdate) between trunc (paaf.effective_start_date) and trunc (paaf.effective_end_date)

and trunc (sysdate) between trunc (peevf.effective_start_date) and trunc (peevf.effective_end_date)

and papf.person_id               = paaf.person_id

and papf.current_emp_or_apl_flag = 'y'

and trunc (sysdate) between trunc (papf.effective_start_date) and trunc (papf.effective_end_date)

and peevf.element_entry_id = peef.element_entry_id

and pivf.input_value_id    = peevf.input_value_id

--and papf.employee_number   = '30987'

group by petf.element_name,

  peef.assignment_id,

  paaf.person_id,

  papf.full_name,

  pivf.name,

  peevf.effective_start_date,

  peevf.effective_end_date,

  peevf.screen_entry_value;