Fusion HCM – Query for Element Entry Details

SELECT *
  FROM per_all_people_f peo,
       per_all_assignments_f asg,
       per_person_names_f per_name,
       per_periods_of_service ser,
       per_person_types_tl per_typ,
       per_legal_employers ple,
       pay_rel_groups_dn payrel,
       pay_assigned_payrolls_dn papd,
       pay_all_payrolls_f pay,
       pay_element_entry_values_f peevf,
       pay_input_values_f pivf,
       pay_element_entries_f peef,
       pay_element_types_f petf,
       pay_entry_usages peu
 WHERE asg.person_id = peo.person_id
   AND ser.person_id = peo.person_id
   AND per_name.person_id = peo.person_id
   AND asg.assignment_id = payrel.assignment_id
   AND asg.period_of_service_id = ser.period_of_service_id
   AND asg.person_type_id = per_typ.person_type_id
   AND asg.legal_entity_id = ple.organization_id
   AND papd.payroll_term_id = payrel.parent_rel_group_id
   AND papd.payroll_id = pay.payroll_id
   AND peevf.element_entry_id = peef.element_entry_id
   AND pivf.element_type_id = petf.element_type_id
   –AND pivf.base_name = ‘Amount’
   –AND petf.base_element_name IN (‘Basic’)
   AND peu.element_entry_id = peef.element_entry_id
   AND peevf.input_value_id = pivf.input_value_id
   AND peu.payroll_relationship_id = payrel.payroll_relationship_id
   AND payrel.group_type = ‘A’
   AND asg.primary_flag = ‘Y’
   AND asg.assignment_type IN (‘E’, ‘C’, ‘N’, ‘P’)
   AND asg.assignment_status_type = ‘ACTIVE’
   AND per_name.name_type = ‘GLOBAL’
   AND ple.status = ‘A’
   AND per_typ.LANGUAGE = USERENV (‘LANG’)
   AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
                           AND per_name.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN payrel.start_date AND payrel.end_date
   AND TRUNC (SYSDATE) BETWEEN papd.start_date
                           AND NVL (papd.lspd,
                                    TO_DATE (’31/12/4712′, ‘DD/MM/YYYY’)
                                   )
   AND TRUNC (SYSDATE) BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+)
   AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
                           AND pivf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN peevf.effective_start_date
                           AND peevf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
                           AND petf.effective_end_date
  • February 15, 2019 | 35 views
  • Comments