Resolve HCM Extract No data error when using bursting
Resolve HCM Extract No data error when using bursting

 320 total views

In this article we will look into the workaround/hack to get rid of the HCM Extract getting failed when no rows are fetched by the HCM Extract.

This is a continuation post to implementing bursting feature for HCM Extract. If you haven’t checked it, please go through it to understand this article better.

When we implement bursting feature, the BI Report tries to burst by Split by even if there is no data and errors out and finally the HCM Extract runs to error because of that. We can see the error on the archive log with message “oracle.apps.financials.payments.shared.util.publicEntity.common.util.security.OPSSKeyStore$6::oracle.security.jps.service.keystore.KeyStoreServiceException: Failed to load the keystore“. At BI Report history, we can see the error as “oracle.xdo.servlet.scheduler.ProcessingException:
There is no data to process

In order to avoid this issue, we need to create some dummy data in the Extract output XML file. But how do we create data in the extract output XML file??

That’s a million dollar question which we are going to solve soon..

Let’s get into the details of how the output is generated by extract to break this logic.

The HCM Extract will archive the data into the pay_action_information table for each Record as part of archiving and then compares it with previous records for changes only processing. Finally it generates the XML file with the output and stores it in PAY_FILE_DETAILS table.

Now, the seeded globalReportsDataModel has the query to pull the information from PAY_FILE_DETAILS table based on the Payroll Action ID specific to that Flow Instance run.

Seeded SQL for globalReportsDataModel:

SELECT source_id payroll_action_id, file_fragment
FROM pay_file_details fd
where ((:payrollActionId is not null
        and source_id=:payrollActionId)
        or
       (:payrollActionId is null
        and :flowInstanceName is not null 
        and source_id in
            (select pact.payroll_action_id 
             from pay_payroll_actions pact,
                  pay_requests req,
                  pay_flow_instances flow
             where flow.instance_name=:flowInstanceName
             and flow.flow_instance_id=req.flow_instance_id
             and req.pay_request_id=pact.pay_request_id
             and pact.action_type not in ('BIP','XRD'))))
and source_type='PPA'
and length(file_fragment)<>0
and sequence= nvl(:sequence,(select max(sequence) from  pay_file_details fd2 where fd.source_id=fd2.source_id and fd2.source_type='PPA' and length(fd2.file_fragment)<>0))

The XML file data will be present in FILE_FRAGMENT field and it consists of the input parameters tags even if there is no data from the extract.

So, we will have to add the dummy tags to the FILE_FRGMENT with an unknown/incorrect KEY value which doesn’t exist in the system in order to satisfy the Split by constraint and avoid hard error.

In order to add the dummy data, we need to get the XML file with data for atleast one employee to check how the tags appear for the KEY value.

XML output with only parameters:

<?xml version="1.0" encoding="UTF-8"?>
<!--Generated by Oracle BI Publisher -Dataengine, datamodel:_New_Hire_notification_dm -->
<DATA_DS>
	<ARCHIVEACTIONID>1593479</ARCHIVEACTIONID>
	<DELIVERYOPTIONID>300000289914115</DELIVERYOPTIONID>
	<PAYROLLACTIONID>1593479</PAYROLLACTIONID>
	<FLOWINSTANCENAME/>
	<SEQUENCE/>
	<ISBURSTING/>
	<G_1>
		<PAYROLL_ACTION_ID>1593479</PAYROLL_ACTION_ID>
		<G_2>
			<FILE_FRAGMENT>
				<NEW_HIRE_NOTIFICATION>
					<REP_CATEGORY_NAME>New_Hire_Notification</REP_CATEGORY_NAME>
					<parameters>
						<request_id>300000290011873</request_id>
						<FLOW_NAME>New_Hire_Notification_1022</FLOW_NAME>
						<legislative_data_group_id/>
						<effective_date>2020-10-22</effective_date>
						<start_date/>
						<report_category_id>300000289914071</report_category_id>
						<action_parameter_group_id/>
						<changes_only>Y</changes_only>
					</parameters>
				</NEW_HIRE_NOTIFICATION>
			</FILE_FRAGMENT>
		</G_2>
	</G_1>
</DATA_DS>

When the extract generates this output, the BI bursting will fail as it can’t find any Split by Key value.

Now, let’s check the output XML with a row of data.

<?xml version="1.0" encoding="UTF-8"?>
<!--Generated by Oracle BI Publisher -Dataengine, <!--Generated by Oracle BI Publisher -Dataengine, datamodel:_New_Hire_notification_dm -->
<DATA_DS>
	<ARCHIVEACTIONID>1592279</ARCHIVEACTIONID>
	<DELIVERYOPTIONID>300000289914115</DELIVERYOPTIONID>
	<PAYROLLACTIONID>1592279</PAYROLLACTIONID>
	<FLOWINSTANCENAME/>
	<SEQUENCE/>
	<ISBURSTING/>
	<G_1>
		<PAYROLL_ACTION_ID>1592279</PAYROLL_ACTION_ID>
		<G_2>
			<FILE_FRAGMENT>
				<NEW_HIRE_NOTIFICATION>
					<REP_CATEGORY_NAME>New_Hire_Notification</REP_CATEGORY_NAME>
					<parameters>
						<request_id>300000290027607</request_id>
						<FLOW_NAME>New_Hire_Notification_1027</FLOW_NAME>
						<legislative_data_group_id/>
						<effective_date>2020-10-27</effective_date>
						<start_date/>
						<report_category_id>300000289914071</report_category_id>
						<action_parameter_group_id>300000148424276</action_parameter_group_id>
						<changes_only>Y</changes_only>
					</parameters>
					<MAIN_DG>
						<OBJECT_ACTION_ID>2117027089</OBJECT_ACTION_ID>
						<MAIN_RC>
							<EMPLID>12345</EMPLID>
							<FIRST_NAME>abcdef</FIRST_NAME>
							<LAST_NAME>ghijkl</LAST_NAME>
							<USER_NAME>12345</USER_NAME>
							<EMAIL>abcdef.ghijkl@gmail.com</EMAIL>
						</MAIN_RC>
					</MAIN_DG>					
				</NEW_HIRE_NOTIFICATION>
			</FILE_FRAGMENT>
		</G_2>
	</G_1>
</DATA_DS>

So, Now we will have to design the dummy XML tags for an invalid KEY value in order to bypass the error.

In the above case, the dummy data would be (We need to get all the tags till KEY after the FILE_FRAGMENT tag):

<NEW_HIRE_NOTIFICATION>
	<MAIN_DG>
		<OBJECT_ACTION_ID>9999</OBJECT_ACTION_ID>
		<MAIN_RC>
			<EMPLID>99999</EMPLID>
		</MAIN_RC>
	</MAIN_DG>
</NEW_HIRE_NOTIFICATION>

Make sure you use an invalid KEY value. In my case 99999 is an invalid person number. If that person number exists in POD and if it gets fetched by extract it might cause an issue. So be aware of it and make sure to use an alpha numeric or numeric key which doesn’t exist in the system.

If we add the above tags to the FILE_FRAGMENT using the below SQL Query, it will generate the dummy data on the Extract Output XML file and the BI bursting will not fail.

select source_id payroll_action_id, pay_report_delivery.get_chunking_clob(:archiveActionId, :payrollActionId, :deliveryOptionId, :sequence, :isChunking) || file_fragment || to_clob('<NEW_HIRE_NOTIFICATION><MAIN_DG><OBJECT_ACTION_ID>9999</OBJECT_ACTION_ID><MAIN_RC><EMPLID>99999</EMPLID></MAIN_RC></MAIN_DG></NEW_HIRE_NOTIFICATION>') file_fragment
from pay_file_details fd
where ((:payrollActionId is not null
and source_id=:payrollActionId)
or
(:payrollActionId is null
and :flowInstanceName is not null
and source_id in
(select pact.payroll_action_id
from pay_payroll_actions pact,
pay_requests req,
pay_flow_instances flow
where flow.instance_name=:flowInstanceName
and flow.flow_instance_id=req.flow_instance_id
and req.pay_request_id=pact.pay_request_id
and pact.action_type not in ('BIP','XRD'))))
and source_type='PPA'
and length(file_fragment)<>0
and sequence= nvl(:sequence,(select max(sequence) from pay_file_details fd2 where fd.source_id=fd2.source_id and fd2.source_type='PPA' and length(fd2.file_fragment)<>0))

The highlighted code is what we have added now. This SQL Query needs to placed in the cloned version of globalReportsDataModel which is used to set the Split by and Deliver by atttibutes.

Now, even when if the extract doesn’t fetch any data, the output XML will be like below with data tags for dummy KEY value and the extract would run to success without archiving any data.

<?xml version="1.0" encoding="UTF-8"?>
<!--Generated by Oracle BI Publisher -Dataengine, datamodel:_New_Hire_notification_dm -->
<DATA_DS>
	<ARCHIVEACTIONID>1593479</ARCHIVEACTIONID>
	<DELIVERYOPTIONID>300000289914115</DELIVERYOPTIONID>
	<PAYROLLACTIONID>1593479</PAYROLLACTIONID>
	<FLOWINSTANCENAME/>
	<SEQUENCE/>
	<ISBURSTING/>
	<G_1>
		<PAYROLL_ACTION_ID>1593479</PAYROLL_ACTION_ID>
		<G_2>
			<FILE_FRAGMENT>
				<NEW_HIRE_NOTIFICATION>
					<REP_CATEGORY_NAME>New_Hire_Notification</REP_CATEGORY_NAME>
					<parameters>
						<request_id>300000290011873</request_id>
						<FLOW_NAME>New_Hire_Notification_1022</FLOW_NAME>
						<legislative_data_group_id/>
						<effective_date>2020-10-22</effective_date>
						<start_date/>
						<report_category_id>300000289914071</report_category_id>
						<action_parameter_group_id/>
						<changes_only>Y</changes_only>
					</parameters>
				</NEW_HIRE_NOTIFICATION>
				<NEW_HIRE_NOTIFICATION>
					<MAIN_DG>
						<OBJECT_ACTION_ID>9999</OBJECT_ACTION_ID>
						<MAIN_RC>
							<EMPLID>99999</EMPLID>
						</MAIN_RC>
					</MAIN_DG>
				</NEW_HIRE_NOTIFICATION>				
			</FILE_FRAGMENT>
		</G_2>
	</G_1>
</DATA_DS>

This is very useful when you implement bursting for HCM Extracts. Most of developers think there isn’t a solution and extract would fail and think it as a product limitation. However with this intelligent workaround we can solve this issue with ease.

If you have faced this issue or know of any other solution, please post them in comments section.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply