HCM Extract – Using Multiple values in Parameter

There is a frequent requirement of using a parameter in HCM Extracts which can accept multiple comma separated values. For example, in Worker Extract, we need to have a Parameter called PER_NUMBER which should accept multiple comma separated values.

To split the input values, we can use REGEXP_SUBSTR function.

SELECT * 
   FROM (SELECT trim(regexp_substr(per_num_param,'[^, ]+',1,LEVEL)) 
           FROM (SELECT pay_report_utils.get_parameter_value('PER_NUMBER') per_num_param 
		           FROM DUAL) 
		CONNECT BY regexp_substr(per_num_param,'[^, ]+', 1,LEVEL)>0)