HCM Extracts Dynamic Output Filename guide

We can set the Output Name field to Dept_%y%m%d%H%M%S which concatenates the date and time in the format yyyymmddhhmmss to the output file while generating the output.

The list of parameters which are supported are listed below:

%y :Displays the sysdate year in four digits: Example: 2011
%m :Displays the sysdate month in two digits: 01-12 (where 01 = January)
%d :Displays the sysdate date in two digits: 01-31
%H :Displays the sysdate hour in two digits based on 24-hour day: 00-24
%M :Displays the sysdate minute in two digits: 00 -
%S :Displays the sysdate number of seconds in two digits: 00 - 59
%l :Displays sysdate milliseconds in three digits: 000 - 999
%edy :Displays the effective date year in four digits: Example: 2011
%edm :Displays the effective date month in two digits: 01-12 (where 01 = January)
%edd :Displays the effective date date in two digits: 01-31
%edH :Displays the effective date hour in two digits based on 24-hour day: 00-24
%edM :Displays the effective date minute in two digits: 00 -
%edS :Displays the effective date number of seconds in two digits: 00 - 59

The difference between %y and %edy is that %y takes the year from the date on which extract runs and the %edy takes the year from the input effective date provided to the extract. Similar logic applies to all other parameters.

Dynamic file name using Run Time Filename option:

In addition the dynamic file name can also be defined at delivery option parameter with name of ‘FILE_NAME_DE‘ (Run Time File Name) which points to a data element, and add the %de to the Output Name and it will include the data_element value to the file name.

We can also use the attribute in the extract as Runtime File Name and concatenate to the output file name by using Output_%de in the output file name.

The parameters %y %m %d %H %M %S gets the time in the UTC format only even if you have changed the user preferences based on your local timezone of CST, EST, PST or IST. However if there is a requirement to derive these parameters in local timezone, then a Fast Formula/table based Valueset should be created and added to the extract to get the time format in the required timezone.

SQL Query to get the time in a different timezone:

select TO_CHAR(sysdate, 'YYYYMMDD', 'NLS_DATE_LANGUAGE=AMERICAN') || to_Char(cast(sysdate AS TIMESTAMP) AT TIME ZONE 'CST', 'hh24miss') DATE_TIME
from dual

If there is a requirement to achieve the daily sequence number of the extract run in the extract then you can define an input parameter to the extract and use the below post-sql which identifies the number of runs of the extract on that particular day and then you can use this in the delivery options of the extract at Run Time File Name.

SQL Query to get the daily sequence of Extract Run:

SELECT COUNT(pfi.flow_instance_id) + 1
FROM pay_flows pf
,pay_flow_instances pfi
,pay_flow_task_instances pfti
,pay_flow_tasks pft
WHERE pf.base_flow_name = ''
AND PF.default_flow_flag = 'Y'
AND pf.flow_id = pfi.base_flow_id
AND pfti.flow_instance_id = pfi.flow_instance_id
AND pfti.STATUS = 'COMPLETED'
AND trunc(pfi.CREATION_DATE) = trunc(sysdate)
AND pft.base_flow_task_name = pf.base_flow_name
AND pft.base_flow_task_id = pfti.base_flow_task_id

The Output Name can be defined as OutputFile_%y%m%d_%de and the Run Time File Name is setup as DailySeq (Extract Parameter for which we write the above post-sql) and at run time the filename will be derived as OutputFile_20200518_1.txt for the first run and OutputFile_20200518_2.txt for the second run on the same day.

Tip: We can achieve dynamic filename in different ways. Directly using parameters in the Output Name, using %de in conjunction with any string in Output Name and then defininf the Run Time File Name as a string or extract attribute or extract input parameter.