Skip Output file of HCM Extract when no data is fetched

2 Shares

In this article we will look into how modifying the HCM Extract not to generate output file when data is not fetched by HCM Extract. Normally a blank file will be generated by the HCM Extract when there is no data fetched by the Extract.

If you want to know the basics of HCM Extract please Click here

Even if there are no rows fetched by the HCM Extract, it will generate the XML file with the input parameters and blank tags for the root data group. So an empty output file will be generated and sent over to the FTP/UCM/delivery option. Normally the output files are sent over to third party systems for processing and most of them do not expect blank files.

For HCM Extracts, we will create a report template and use the Global Reports Data Model from Shared Folders/Human Capital Management/Payroll/Data Models folder. We will have to copy the GlobalReportsDataModel to Custom folder where the BI Report resides and then we will have to modify it as per the below steps.

Table of Contents

The default SQL from that Data Model will look like:

SELECT source_id payroll_action_id	,pay_report_delivery.get_chunking_clob(:archiveActionId, :payrollActionId, :deliveryOptionId, :sequence, :isChunking) || file_fragment 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
			))

Now we will add another condition to it to fetch data from the data model only when there is data from the root data group.

Modified Version of SQL from the Data Model:

SELECT source_id payroll_action_id
	,pay_report_delivery.get_chunking_clob(:archiveActionId, :payrollActionId, :deliveryOptionId, :sequence, :isChunking) || file_fragment 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
			))
	AND instr(file_fragment, '<Person_Number>') <> 0

Consider we have the attibute <Person_Number> in the root data group and if the extract fetches data, we should have atleast one tag with this name and hence we have added the condition “and instr(file_fragment,'<Person_Number>’ ) <> 0” at the end of the query in the Data Model.

Similarly we will also need to modify the bursting query attached to the GlobalReportsDataModel.

The default Bursting SQL from that Data Model will look like:

SELECT :payrollActionId KEY
	,del_opt.bip_template_name TEMPLATE
	,del_opt.output_type OUTPUT_FORMAT
	,pay_report_delivery.get_output_file_name(del_opt.output_name, pact.effective_date, del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id, :sequence - 2) OUTPUT_NAME
	,del_opt.calendar_code CALENDAR
	,'true' SAVE_OUTPUT
	,decode(del_opt.delivery_type, 'NONE', NULL, 'HCMCONNECT', NULL, 'INBINT', NULL, del_opt.delivery_type) DEL_CHANNEL
	,pay_report_delivery.get_del_param_value('1', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER1
	,pay_report_delivery.get_del_param_value('2', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER2
	,pay_report_delivery.get_del_param_value('3', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER3
	,pay_report_delivery.get_del_param_value('4', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER4
	,pay_report_delivery.get_del_param_value('5', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER5
	,pay_report_delivery.get_del_param_value('6', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER6
	,pay_report_delivery.get_del_param_value('7', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER7
	,pay_report_delivery.get_del_param_value('8', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER8
	,pay_report_delivery.get_del_param_value('9', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER9
	,pay_report_delivery.get_del_param_value('10', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER10
	,pay_report_delivery.get_del_param_value('LOCALE', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) LOCALE
	,pay_report_delivery.get_del_param_value('TIMEZONE', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) TIMEZONE
FROM pay_payroll_actions pact
	,per_ext_delivery_options_b del_opt
WHERE pact.payroll_action_id = nvl(:archiveActionId, :payrollActionId)
	AND del_opt.ext_delivery_option_id = :deliveryOptionId

Now we will add another condition to it to fetch data from the data model only when there is data from the root data group.

Modified Version of Bursting SQL from the Data Model:

SELECT :payrollActionId KEY
	,del_opt.bip_template_name TEMPLATE
	,del_opt.output_type OUTPUT_FORMAT
	,pay_report_delivery.get_output_file_name(del_opt.output_name, pact.effective_date, del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id, :sequence - 2) OUTPUT_NAME
	,del_opt.calendar_code CALENDAR
	,'true' SAVE_OUTPUT
	,decode(del_opt.delivery_type, 'NONE', NULL, 'HCMCONNECT', NULL, 'INBINT', NULL, del_opt.delivery_type) DEL_CHANNEL
	,pay_report_delivery.get_del_param_value('1', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER1
	,pay_report_delivery.get_del_param_value('2', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER2
	,pay_report_delivery.get_del_param_value('3', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER3
	,pay_report_delivery.get_del_param_value('4', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER4
	,pay_report_delivery.get_del_param_value('5', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER5
	,pay_report_delivery.get_del_param_value('6', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER6
	,pay_report_delivery.get_del_param_value('7', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER7
	,pay_report_delivery.get_del_param_value('8', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER8
	,pay_report_delivery.get_del_param_value('9', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER9
	,pay_report_delivery.get_del_param_value('10', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) PARAMETER10
	,pay_report_delivery.get_del_param_value('LOCALE', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) LOCALE
	,pay_report_delivery.get_del_param_value('TIMEZONE', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id) TIMEZONE
FROM pay_payroll_actions pact
	,per_ext_delivery_options_b del_opt
WHERE pact.payroll_action_id = nvl(:archiveActionId, :payrollActionId)
	AND del_opt.ext_delivery_option_id = :deliveryOptionId
	AND EXISTS (
		SELECT 1
		FROM pay_file_details fd
		WHERE sou rce_id = :payrollActionId
			AND sequence = nvl(:sequence, (
					SELECT max(sequence)
					FROM pay_file_details fd2
					WHERE fd.source_id = fd2.sour
						AND fd2.source_type = 'PPA'
						AND length(fd2.file_fragment) <> 0
					))
			AND instr(file_fragment, '<Person_Number>') != 0
		)

I have highlighted the added query. It will skip if there is no data found in root data group.

Finally the BI Report needs to be mapped to the GlobalReportsDataModel present in Custom folder instead of the Shared Folders one.

Now if you run the extract and if it doesn’t fetch any data, then output file will not get generated at all.

Tip: You can use this functionality in critical interfaces where the third party systems(banks/insurance) doesn’t accept the blank files to be placed on their server for processing.

2 Shares

We have tried this method successfully. But one observation was, when there is no data FTP delivery BI report ends in error status. So how to modify the status? Error alarms client. Any idea please

Sricharan Monigari says:

September 10, 2024 at 1:45 PM

Ideally, the FTP shouldn’t get triggered if the bursting result has no data. However, if it is getting triggered, I don’t think there is any option

- Veena says:
  
  September 10, 2024 at 1:58 PM
  
  Report still gets called as it is added as delivery option to extract. Extract ends in completed with alert status and FTP delivery fails for no data.
  As an alternative, I have added the condition to check data only in bursting query, not in main dataset. This resulted in extract completing success status. FTP gets triggered only when there is data.
  
  - Sricharan Monigari says:
    
    September 10, 2024 at 2:02 PM
    
    so you removed the exists condition in the main dataset and only added it to bursting query and it worked.. if we add it both, then also it should work. I am not sure why it did not work for you..
    
    - Veena says:
      
      September 10, 2024 at 2:12 PM
      
      I wasnt saying that it did not work, but extract ended in completed with alerts status and BI report failed. We dont want this status as this might be a false alarm for client

In visible box by plugintheme

The default SQL from that Data Model will look like:

Modified Version of SQL from the Data Model:

The default Bursting SQL from that Data Model will look like:

Modified Version of Bursting SQL from the Data Model:

You Might Also Like

Achieve complex requirements using Custom Global Reports Data Model

SQL Query to find scheduled HCM Extracts

How to capture Vendor name in HCM Extracts post 21D update

Quick Links