Skip Output file of HCM Extract when no data is fetched

  • Post category:HCM Extract
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:10 mins read
Tags: BI Publisher, HCM Extract, SQL Query
You are currently viewing Skip Output file of HCM Extract when no data is fetched
Skip Output file of HCM Extract when no data is fetched
Share
Tweet
Share
Pin
9 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.

Share
Tweet
Share
Pin
9 Shares