In this article we will look into getting the contents of file on UCM using BI Report.
Normally the vendor input files are uploaded to UCM and then they would be picked up for processing by HDL or payroll flows to process the file and load data into fusion application.
Sample use case would be the payroll batch loader automation where in the vendor file is uploaded to UCM and then processed using Load batch from file and transfer batch flows. PBL is being decommissioned by 20D and hence we can use similar functionality for HDL transformation.
If the third party system is preparing the DAT file and putting in the zip file on UCM, then you can use the Initiate Data Loader flow to upload the file to HDL.
Now if there are multiple vendors and if the individual files are getting uploaded to UCM. And you want to consolidate all of them into a single file and initiate the process only once.. Can you do it? Or can the HDL formula read two input files?
The HDL formula flow (Load data from file) can take only one Content ID as input. So if there are multiple files, then you need to submit the process multiple times.
So, it boils down to getting a single file on the UCM which we can achieve using BI Report?? Do you believe it?? You can read the contents of the files in UCM and append them to make a single file and upload it back to UCM.
Note: Currently it is possible to read only the .TXT files from UCM in BI Report. The file extension should be .txt only. It cannot read the .csv, .dat, .zip etc. So you can request the vendor to send in .txt format only and mostly all vendors support multiple formats and it shouldn’t be an issue.
Now Let’s look into how to access files on UCM in BI Report.
Let me upload two text files on to UCM.
File1.txt LABC2020/01/014712/12/31 UBICACION 1HCM001COMMONCOA BBCD2020/01/014712/12/31 HCM001COMMONPUESTO 1FULL_TIMEAREG File2.txt Regular EarningsUS Legislative Data GroupE142019/06/292019/07/12 Overtime EarningsUS Legislative Data GroupE122019/06/292019/07/12
Now we will access these files using BI Report and combine them into single file and upload it back to UCM. And then it can be used by any other flows which is beyond the scope of this article.
Now let’s navigate to BI Report and create the datasets using Content Server.
Pre-requisite to add the Content Server Data Set is there is should be another SQL dataset which should have the document id fetched. And then the Content Server data set will link to the SQL data set and get the document content for the document id set by the other data set.
Data Model Snapshot:
G_1 SQL Query and Snapshots:
select 'Dummy' Key, a.* from revisions a where ddocname in (:CONTENT_IDS)
G_2 UCM Snapshots:
Here the DID from G_1 should be selected in the Document ID. Which will be used as key to get the document text from UCM.
We will add a parameter to get the multiple content ids:
Now, we will go to Data page and test it for a couple of Content IDs: UCMFA02586299,UCMFA02586300
Now a simple etext template can be created to append all DOCUMENT fields from G_2 level.
Now, if we run the report for the Content IDs, we get the output like below:
Now, as we got the consolidated output, next step is to write the bursting query to upload the file to UCM
SELECT 'Dummy' as KEY -- Split Key from data model SQL ,'Test' template -- Report template name ,'en-US' locale ,'TEXT' output_format -- Output type ,'WCC' del_channel -- To send to content server ,'FA_UCM_PROVISIONED' parameter1 -- server name (BI Administration > Manage BI Publisher > Delivery > Content Server) ,'FAFusionImportExport' parameter2 -- security group name ,'HCM_IMPL' parameter3 -- Author of the file ,'Consolidated File1&2' parameter5 -- Title ,'File3' parameter6 -- Output file name ,'' parameter7 -- Comments (Optional) ,'' parameter8 -- Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one ,'false' parameter9 -- Custom metadata (true/false). Specify ‘false’ ,'' parameter10 -- Folder Path FROM dual
Once bursting query is setup, we need to enable the bursting option in the BI Report properties.
Then we can schedule this report with the input parameters to test this functionality.
Click on More > Schedule for BI Report
We need to schedule the report since bursting doesn’t work if we open the report. Only with schedule/through payroll flow, bursting will work.
Consolidated File Contents:
Finally we are done with the demonstration of the BI Report to get the UCM file contents and display on the output.
Hope this helps when you get similar requirement.
Catalog files for reference: Download Link
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM