BIP – Extract Union Details from Assignment

SELECT paam.assignment_number
      ,paam.labour_union_member_flag
      ,houft.name
  FROM per_all_assignments_m paam
      ,hr_organization_units_f_tl houft
 WHERE paam.assignment_type ='E'
   AND paam.union_id =  houft.organization_id
   AND houft.language = 'US'
   AND paam.effective_latest_change = 'Y'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN houft.effective_start_date AND houft.effective_end_date