In this post we will look into the most frequently asked interview questions on HCM Extracts.
Want to learn Fusion Technical tools checkout this article.
If you haven’t gone through the extracts related posts, please go through them.
- Basics of HCM Extracts in Fusion HCM
- Resolve A system error occurred during processing in Extracts
- Configure GMFZT logging for an HCM Extract
- New features in HCM Extracts in 19B release
- Importing Changes Only HCM Extract post 20A update
- Scheduling and Cancelling an existing schedule of HCM Extract
- Skip Output file of HCM Extract when no data is fetched
- Basics of Changes Only Functionality in HCM Extract
- Using Table based Valueset when DBI is unavailable
- Most frequently used User Entities in HCM Extracts
- How to view the output of HCM Extract run by other users?
- How to link/connect the User Entities in HCM Extracts
- Fix for assertion failure error in HCM Extracts
- Top 50 Interview questions and answers on HCM Extracts
- HCM Extracts Dynamic Output Filename guide
- SQL Query to find scheduled HCM Extracts
- Achieve complex requirements using Custom Global Reports Data Model
- Payroll Flow and HCM Extracts Mapping for Payroll Module
- Hiding XML nodes in HCM Extract Output
- Running the latest extract version using Payroll Flow
- How to convert HCM Extract Export XML to Readable format?
- List of User Entities, DBIs, Routes and Contexts from R13 19D
- Deleted Data Report using Audit Functionality
- Passing Logged in User details to HCM Extract
- How to handle FF not compiled error for HCM Extracts
- How to handle Daylight Savings timings for HCM Extract schedules
- HCM Extracts Design and Run Time Diagnostics Reports
- Implementing Bursting feature for HCM Extract
- Resolve HCM Extract No data error when using bursting
- Now we can extract HDL Import and HCM Extract Run Details in OTBI from 20D
Table of Contents
What is the difference between changes only extract and Full extract?
Full extract means whenever the extract is run, it will fetch the entire employee information as per the criteria defined in the extract. Changes only extract means it will fetch only the employees who had changes since the last run.
Can we cancel an existing schedule of other user?
No we cannot cancel the schedule of other user. Only the user who has scheduled the extract will have access to cancel it.
Can we schedule extract on ad-hoc basis like every alternative day, only on weekends or only on weekdays or 2 days after pay period or 2 days before pay period dates?
Yes, we can schedule extract on ad-hoc basis using Flow Schedule Formula. You can used some advanced coding using table based valuesets in Flow Schedule formula to achieve these requirements.
A changes only extract is developed and is running on daily basis. Some employees should have been on the output but are not present? How do you debug this?
We need to look into the UE and the advanced filter criteria and prepare SQL Query and run it in BI Report to check if those employees are getting picked up or not. If they are not getting picked up then they aren’t meeting the criteria for the extract.
If they are getting picked up by the SQL Query and still not on the output, then you need to check the attribute level flags.
- If the field which has changed has been flagged as “Exclude from Comparison” in which case the employee won’t get picked up by the extract.
- If the field has a Required flag checked and the attribute value is blank for the employee. In this case also as the value is blank, it will skip the employee row completely.
If there are three delivery options (FTP/EMAIL/UCM) for an extract and you want to trigger only one delivery option when you run the extract? Is it possible?
Yes, it is very much possible. We need to create multiple Report Categories in the HCM Extract. Buy Default there will be only one Report Category and it will be tagged to all delivery options. We need to create a custom Report Category and add the required delivery option to it ex. FTP. And while running the extract we need to select that custom Report Category and then only the FTP option will be triggered and not the other ones.
Is it possible to get the XML output of an HCM Extract if XML option is not setup at the delivery option level?
Yes, It is still possible to get the XML output file of the extract even if it is not setup at the delivery option level. If there is atleast one delivery option added with a BI Report, you can navigate to the BI Report, Click on More and then history.. then clear the owner name from your username to blank and click on search. You will see all the extract run instances. You can click on the latest run instance and you will get an option to download the xml file and also the output file.
If at all there is no delivery option at all added to the extract, then navigate to GlobalReportsDataModel (/Shared Folders/Human Capital Management/Payroll/Data Models) path and provide the payroll action id(you can get it from HCM Extract log files) and you would get the XML output which you can copy into an XML file.
HCM Extract has been developed with a changes only parameter, however it is pulling all employees every time? What could have gone wrong?
There could be multiple reasons for this issue:
- Threading Database Item is not set correctly
- Keys are not defined at Record level
- Changes only parameter is defined incorrectly
We have a Changes Only extract with a Person Number parameter and if no person number is present then it will run for all employees. Baseline has already been created and the extract is running in changes only mode daily. What will happen if we do an ad-hoc run for a single person with all attributes mode?
If we run the extract for a single person number with all attributes mode, then the baseline will get changed only for this employee and it will not impact other employees which already exist in the baseline. In the output file, we will get only this employee for which the extract is run. From the next daily changes only run, the extract would pick up the changes normally.
Changes only extract is scheduled daily and there is a requirement to add a new attribute. What should be the approach?
Any new attributes addition to the Extract will impact the baseline. When we add the new attribute, even if we run the extract in changes only mode, it will still run as all attributes mode as it finds a change in value for the newly added attribute for all employees.
So the best approach would be to do the change to extract immediately after the daily schedule so that none of the changes are skipped. Immediately after the changes are completed, we can do a baseline without delivery options so the file is not sent over to the vendor. From next run onwards it will pick up the changes as usual from previous day.
Full Extract has been scheduled daily but it is picking up old data every time and it is giving same result even though there are new employees who should be part of the file. What could be the issue?
If we are seeing old data in the file every time, the issue will be with the input date that is being passed to the HCM Extract. While scheduling we need to add a Post-SQL to Effective Start Date Parameter to pick up the system date (select sysdate from dual). if that is not done, then during scheduling whatever date is selected as input date, every schedule will pick up the same date and will result in old data.
Can we combine two extracts and initiate them with a single run?
Yes. It is possible to combine two extracts and run them at once. We need to create a custom payroll flow and add the two extracts. Then we need to add the parameters to the payroll flow and map the extract parameters to the flow parameters.
Can we check the baseline tables for changes only extracts?
No. it is not a straight forward table where the information is stored. And it is not stored with a single date. Every day when the extract runs, it will insert data into the baseline with the changes keyed by the payroll action id. So there would be many rows for employees with multiple payroll action ids and we cannot build a query to get this information.
Again Yes. It is possible to achieve it using XSL transformation template. XSL will transform the extract output XML to another XML file and we can skip all the junk content which is not required by the client.
Another option would be to clone the GlobalReportsDataModel and write SQL Query to fetch the data from FILE_FRAGMENT and break it into pieces using XMLTABLE command and select only the fields which are of interest.
How to improve the performance of the extracts when they are running for longer times?
We can do it using process configuration groups. At payroll process configuration group level, we can define multi-threading option and specify the number of threads that can be run in parallel.
Can we burst and upload the extract output files into Document of Records?
Yes. It is possible to burst and upload the output files into Document of Records. There is documentation available on Cloud Customer Connect.
Can we pass multiple person numbers to hcm extract as input?
Yes we can use free text field as input and the person numbers can be separated by comma. In the advanced filter, we can use regexp_substr and convert the comma separated values into rows and can be used in the IN Clause for Person Number.
Extract output is showing up on the output screen only if the output size is less than 10MB. Is it the seeded functionality or can we override it?
By default the HCM Extract uses the “Default Group” as the process configuration group if we don’t select anything for it while running it. By default the output size is setup as 10MB. So for any files that are more than 10MB in size, the output will be shown as a hyperlink to UCM which when clicked will download the file. There is an Maximum BI Output size which can be overridden at the Payroll Process Configuration group level which should increase the default size and the output should be visible on the results.
Can we get the translation information from HCM Extracts?
Yes we can get the translation information from HCM Extracts. However there are only a few UEs which are available currently which fetches the output in different languages.
Can we send the output of the extract to the email address of the user who has run it?
Yes we can achieve this by adding an input parameter for the extract and populate that parameter using a post-sql command using session variable FND_GLOBAL.USER_NAME. Then in the delivery options use Email Delivery option and select the email address as the input attribute. In this way we can send the output dynamically to the user who is running the extract.
Will the changes only extract pull data when an employee is purged?
Extract even though it is changes only will not pull the deleted data. It will fetch only the additions and changes to existing data. If the Audit is enabled, then the deleted data would be available in those tables and can be reported based out of BI Report. However in case of purging employee, i think the data will also get purged from audit tables.
What is the difference between the ‘Assignment Status Type’ and the ‘Extract Employee Assignment Status Type’ attributes? How do you know which one to use?
The ‘Extract Employee Assignment Status Type’ attribute is a DBI which is directly associated with the user entity when defining the block. The ‘Assignment Status Type’ attribute is a DBI group which is associated with a different user entity, and is accessible because of contexts that have been set (indirectly derived).
Directly associated DBIs are more efficient compared to indirect. Therefore, it would be better to use the ‘Extract Employee Assignment Status Type’ attribute rather than the ‘Assignment Status Type’ attribute because of performance considerations.
Can we replace a field with another attribute conditionally in HCM Extract?
Yes. It can be achieved using the Conditional Replacement option at attribute level. You can specify a condition and which when satisfied it will use that attribute instead of the attribute selected in the DBI.
Can we get the output of HCM Extract in JSON format?
Yes we can achieve it using RTF/e-text template. HCM Extract as such will generate output in XML format and we can use any template to convert it into required format.
A full extract has to generate some atleast 1000 employees of data and it is fetching only very less employees? What could be the issue?
We need to analyze the UE SQL along with the advanced filter Criteria in the Data Model and check if the extract is fetching those employees. If the SQL is not fetching rows, then it’s filter issue. If the SQL is returning rows, then there should be some issue with the template. In e-text template we can add additional criteria to filter employees and write only specific employees to output file who satisfies the filter criteria. Depending on the requirement we might need to remove the additional filter at template level to get all employees.
Can we encrypt only some fields on the extract output? Like PII information like SSN, phone?
No. We cannot achieve encryption level at attribute level. However if the SFTP server supports pgp encryption, then the complete output file can be encrypted by placing file on SFTP server.
After P2T refresh, is there any automated way to update the extracts to change the FTP path?
No. It is not possible to change the FTP server and path details automatically. We need to do it manually for all extracts. If we don’t change it and if someone accidentally runs it, the file will sent over to vendor and it will be a huge mess to clean up the third party system and get to normalcy. Other alternative would be to change the configuration settings for FTP server in BI administration to make that option don’t work at all.
Can we generate dat files out of extract and use them to load using HDL?
Yes. We can use the etext template to get the output in pipe delimited format and then setup the Inbound Interface delivery option to override the file name to .dat and then zip it. Finally we need to add the Initiate HCM Data Loader task to the payroll flow to kick off the HDL process.
There is an Extract Rule Fast Formula defined in extract. The extract logic uses an attribute, logic is perfect and is compiled successfully. Still it is not returning any value on the XML file. What could be the reason?
If the logic is intact and the formula is compiled and the extract also compiled and validated then the only reason that it would fail is that the attribute which is being used by the Fast Formula is not available at the time of formula being executed. If the actual attribute is at sequence 10, then the attribute with FF can be added at any sequence above 10 and not below 10 in that record. If the FF attribute is added at 8 sequence, then the attribute used in the FF will not have value at that point of time and hence the FF will return blank value.
Can we use two setup UEs in a single extract without a join between them?
Yes. We can create an extract based out of the setup UEs which can be used as Root Data Groups. However we cannot have two Root Data Groups. So the solution is to have the PER_EXT_REST_UE as root and connect it to the setup UEs as child data groups which solves the purpose.
An HCM Extract has been setup with email bursting functionality and when the extract runs the email is being sent over to 5 employees and not all 20 employees? What could be the reason?
While setting up Email bursting we need to clone the GlobalReportsDataModel and modify the Split by and deliver by fields in the bursting tab to have the exact tag with complete XPATH in these fields. if this is not right then even those 5 employees wouldn’t get the email right? So the issue is something else.
Now get back to delivery options and check all attributes which are requried fields for email delivery option. The From email address is mandatory even though the documentation doesn’t state that. You can use any client email id or [email protected] if you don’t want to use client email in the from clause. As the from was not setup, system will randomly send email address to some and not all.
Questions for which you can get answers in my previous articles:
- Can a user check the output of the extract run by other user?
- How to schedule/cancel schedule of HCM Extracts?
- How to enable logging for debugging purposes in HCM Extracts?
- Difference between Changes Only, Changes only with marked attributes?
- Difference between Mark as Changed and Always display options?
- Why do we use exclude from comparison option?
- What are the most frequently used User Entities in HCM Extracts?
- How do you link Data Groups and how to know the contexts set and required?
- What is the use of PER_EXT_RESET_UE and where is it used?
- What would be the approach if a DBI isn’t available?
- Is it possible to skip the output if data isn’t fetched by the extract?
- Can the extract be run using SOAP services?
- Can we use flexfields in HCM Extracts?
Other questions for which answers will be posted later (Looking for audience to answer some of these in comments section):
- When we use bursting for HCM Extract and if there is no data fetched the extract will error out? Is there a way to handle this scenario?
- Can we have an auto incremented file name? If an extract is run two times today, the first file should have Output File1.txt and next one should have Output File2.txt. Is it possible?
- Can we have a dynamic file name built for HCM Extract with combination of date and time in a different timezone other than the UTC which is by default?
- A changes only extract should trigger notifications when an employee is hired. However the notification is getting triggered when the employee is hired and another notification is being sent over when the employee data is updated. How to fix this issue?
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
Nice Blog and very informative
Very good blog when ever I have time I am reading your blogs.
I have couple of question here one is how to get UE SQL and second one is When calling BI report(etext template) in dummy extarct for HDL in the result we are getting header of the template not the values can you please suggest to get the values.
Can you please reach me through mail: [email protected] or 9949467612. Or share your details I will call you.
For the first one Data exchange-view user entity details and search for the UE.. open the UE and in the right side u can see a tab called SQL.
Thanks for replay. Any idea about second one.