Query to Get Element Retro Details from Element Definition

SELECT petf.element_type_id,

       petf.element_name,

       peg.event_group_name,

       prcu.retro_component_usage_id,

       pesu.retro_element_type_id,

       petf_retro.element_name retro_element_name

  FROM pay_element_types_f petf,

       pay_event_groups peg,

       pay_retro_component_usages prcu,

       pay_element_span_usages pesu,

       pay_element_types_f petf_retro

 WHERE     UPPER (petf.element_name) = 'PUNISHMENT DEDUCTIONS'

       AND petf.recalc_event_group_id = peg.event_group_id

       AND prcu.creator_id = petf.element_type_id

       AND pesu.retro_component_usage_id = prcu.retro_component_usage_id

       AND petf_retro.element_type_id = pesu.retro_element_type_id