• Post category:Fast Formula
  • Post comments:0 Comments
  • Post last modified:November 23, 2020
  • Reading time:4 mins read
You are currently viewing Passing Parameter values from Flow Instance to HDL Transformation Formula
Passing Parameter values from Flow Instance to HDL Transformation Formula

In this article we will look into resolving the pain point of passing the Flow Instance Parameter values into the HDL Transformation formula.

Normally for other formulas like Extract Rule, we can directly use the parameters of flow using GET_PARAMETER_VALUE(‘Changes_Only’) and GET_PARAMETER_VALUE_DATE(‘EFFECTIVE_DATE’) functions. However these functions don’t work in “HCM Data Loader” type transformation formula.

If there is a requirement to load the element entries and want to compare the input file date with the input effective date on the flow, or if you want to pass some other parameter from the flow into the transformation formula then you are struck there without clue.

Let’s look into details on how to achieve this requirement.

Our approach would be to get the values from flow parameter values table using the table type value set for the flow instance id which is running now.

We will use the session attribute FND_GLOBAL.get_session_attribute(‘JOB_REQUEST_ID’) to get these details.

Table of Contents

SQL for Valueset to get the flow parameter values:

SELECT pv.flow_param_value
FROM pay_flow_instances fi,
	pay_flows_vl f,
	pay_flow_parameters_vl fp,
	pay_flow_param_values pv,
	pay_requests prq,
	ESS_REQUEST_HISTORY erq
WHERE f.base_flow_id = fi.base_flow_id
	AND fp.base_flow_id = f.base_flow_id
	AND f.base_flow_id = f.flow_id --get only core records
	AND pv.flow_instance_id = fi.flow_instance_id
	AND fp.base_flow_parameter_id = pv.base_flow_parameter_id
	AND fp.base_flow_parameter_id = fp.flow_parameter_id --get only core records
	AND fi.flow_instance_id = prq.flow_instance_id
	AND prq.call_id = erq.absparentid
	AND prq.call_type = 'FLOW_ESS'
	AND fp.base_flow_parameter_name = ':{PARAMETER.PARAMETER_NAME}'
	AND erq.requestid = FND_GLOBAL.get_session_attribute('JOB_REQUEST_ID')

Next step is to call this Valueset in the transformation formula and pass the PARAMETER_NAME attribute.

Sample format on the Fast Formula:

l_parameter = GET_VALUE_SET('VALUESET_VS','|=PARAMETER_NAME=EFFECTIVE_DATE')

This way we can get the parameter values in the Fast Formula and then we can do calculations based on this input value.

Hope you got to learn something new today. If you have any other ideas or any other use cases where this can be used, please let me know in comments section.