SQL statement of Value set

Summary

Help on build the SQL statement

Content

Hi all, 

 

I need help on build the SQL of Value set  to get Action Type (preferred) or Action Type ID. It should do something like that:

SELECT pav.ACTION_TYPE_ID FROM per_actions_vl pav,
      per_all_assignments_m paam
   WHERE pav.action_code = paam.action_code
   AND to_date(:{PARAMETER.L_EFF_DATE},'YYYY/MM/DD')  BETWEEN paam.effective_start_date AND paam.effective_end_date
      AND to_date(:{PARAMETER.L_EFF_DATE},'YYYY/MM/DD') BETWEEN pav.start_date AND pav.end_date
      AND paam.assignment_id = :{PARAMETER.P_ASSIGNMENT_ID}