In this article, we are going to learn how to set output name dynamically based on parameter values of an HCM Extract Flow instance. HCM Extract – we used in this article is using BIP to burst multiple files. So, we need to a way to send the parameter values selected at the time of HCM Extract Schedule.
Solution
Modify the bursting query from globalPayrollDataModel, to derive output name based on flow parameters. Code snippet is attached below:
,(case when regexp_replace(regexp_substr(LEGISLATIVE_PARAMETERS,’*[|UNION_NONUNION=*|]+$’), ‘UNION_NONUNION=’,”) = ‘|NU|’ then
‘XXX_NonUnion_’ || REPLACE(substr(LEGISLATIVE_PARAMETERS, instr(LEGISLATIVE_PARAMETERS, ‘EFFECTIVE_DATE=’)+15,10), ‘-‘,”)
when regexp_replace(regexp_substr(LEGISLATIVE_PARAMETERS,’*[|UNION_NONUNION=*|]+$’), ‘UNION_NONUNION=’,”) = ‘|U|’ then
‘XXX_Union_’ || REPLACE(substr(LEGISLATIVE_PARAMETERS, instr(LEGISLATIVE_PARAMETERS, ‘EFFECTIVE_DATE=’)+15,10), ‘-‘,”)
end) OUTPUT_NAME
above case statement generating the output name based on the LEGISLATIVE_PARAMETERS column from PAY_PAYROLL_ACTIONS table.