Introduction
In one of the previous article (Generating EText Output From HCM Extracts In Oracle Fusion HCM Cloud Application) we have seen how we can generate EText Data from HCM Cloud Application. One can even get eText Output from BI Publisher Report. But the major limitation in both the approach is that the output file extension is .TXT and one would need other means to have .DAT file.
If the generated file is prepared with the intent of loading the data back into Oracle HCM Cloud Application using HDL one can do same using Loading Data into Oracle HCM Cloud Using Inbound Interface Delivery Option
In this post we will demonstrate how can one generate eText file with .DAT extension from Oracle HCM Cloud Application. However, the only limitation at this point of time is that the .DAT file will be under a zip folder.
For this example, we will have to create a BI Data Model which will act as the Data Source and an HCM Extract. While the data would be fetched from the BI Data Model we would need to make use of an HCM Extract as we would need the “Inbound Interface” delivery option to assign the .dat extension to the generated output file.
We would create a Dummy Extract named “DummyExtract” which will have just one attribute.
We would have one data group namely DummyDataGroup which is also the root data group
Some Details related to Data Group are in table below:
DataGroupName |
UserEntity |
DataGroupFilterCriteria |
ExtractRecords |
DummyDataGroup |
PER_EXT_PAY_EMPLOYEES_UE |
PersonRecord |
Once we have all these details populated we would need to define the Extract Delivery Options. Details for which are in below table
Attribute Name |
Attribute Value |
Start Date |
1/1/2000 |
End Date |
12/31/4712 |
*Delivery Option Name |
PersonData |
*Output Type |
Text |
Report |
/Custom/Practice Samples/PersonDataReport.xdo |
Template Name |
PersonDataTemplate |
*Output Name |
Worker |
*Delivery Type |
Inbound Interface |
Required |
Checked |
Encryption Mode |
None |
Override File Extension |
.dat |
Integration Name |
PersonData |
Integration Type |
Data Loader |
Compressed Delivery Group |
PersonData.zip |
Once we are done with the above setup we will get the output file (.txt for this example) but we are trying to generate .DAT file and so to ensure we get the file with same filename we will have to perform the following steps:
-
Add a parameter named “Auto Load” to the DummyExtract
-
Add “Initiate HCM Data Loader” to DummyExtract payroll flow
Add “Auto Load” Parameter to DummyExtract
We would need to add a new parameter. Details of the parameter are in below table
Attribute Name |
Attribute Value |
Sequence |
10 |
Name |
Auto Load |
Tag Name |
Auto_Load |
Data Type |
Text |
Display |
Yes |
*Note: We always have to use ‘N’ as the value of this parameter for this specific scenario or else it will try to load data back into application (which is not the ask here)
Add Initiate HCM Data Loader Payroll Flow to DummyExtract Payroll Flow
As a next step we would also need to Add “Initiate HCM Data Loader” Payroll flow as part of DummyExtract Payroll Flow. We can either navigate to Payroll->Checklist->Search for DummyExtract Payroll Flow Pattern or use Refine Extracts->Search for DummyExtract
Once the search results are retrieved we need to click on “Edit” (pencil icon) and choose the following task
*Please make sure to select the payroll flow which has the description of “Generate HCM Data Loader File and optionally perform a Data Load” as the other payroll flow task which has similar name will not serve the purpose here (both have different definition)
Once we add the payroll flow task we should click on “Go To Task” and add the following details:
Initiate HCM Data Loader Task Definition: Basic Information (Data Loader Archive Action) |
|
Name |
Data Loader Archive Action |
Execution Mode |
Submit |
Data Type |
Text |
Parameter Basis |
Bind to Flow Task |
Basis Value |
DummyExtract, Submit , Payroll Process |
Usage |
Input Parameter |
Initiate HCM Data Loader Task Definition: Basic Information (Data Loader Configuration) |
|
Name |
Data Loader Configuration |
Execution Mode |
Submit |
Data Type |
Text |
Parameter Basis |
Constant Bind |
Basis Value |
ImportMaximumErrors=100, LoadMaximumErrors=100, LoadConcurrentThreads=8, LoadGroupSize=100 |
Usage |
Input Parameter |
Now the setup part from an HCM Extract piece is completed.
*Note: One point to note here is that the Report we have used here /Custom/Practice Samples/PersonDataReport.xdo is not using globalReportsDataModel but a custom Data Model.
Details of PersonDataReport (Data Source)
Contrary to the normal approach where we use globalReportsDataModel in HCM Extract here we will have to create a report which would be based on custom Data Model. The SQL query for the custom data model is mentioned below:
SQL Query for PersonData Data Model |
select papf.person_number personnumber, to_char(papf.start_date, 'YYYY/MM/DD') hiredate, to_char(papf.effective_start_date, 'YYYY/MM/DD') effectivestartdate, to_char(papf.effective_end_date, 'YYYY/MM/DD') effectiveenddate from per_all_people_f papf where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and papf.person_number IN ('7','10','13','39') |
We would create a Report from this data model which will have the following eText Template attached.
And when we run the report we can see data as below:
Running Dummy Extract
Now as a last step we will run the Dummy Extract and check if we get the output in .dat file
If we go to the “Extract Delivery Options” tab we can see all the output files generated.
If we click on “DL_PERSONDA_1460063” a zip file will get downloaded
And when we unzip the file we can see a .dat file
Summary
So this is how one can get .dat file from Oracle HCM Cloud Application using BI Publisher Data Model as a Data Source. One can use any SQL query which would serve as the data source (no matter whether the application module is HCM , Finance or something else).The only limitation at this point is that the Report needs to be static (that is cannot accept user input) but probably in near future we will get a workaround for that too. Till then we may use this technique to generate .DAT file extension from BI Publisher.