You are currently viewing Solve manual FTP server update issue for HCM Extracts post every P2T refresh
Solve manual FTP server update issue for HCM Extracts post every P2T refresh

In this article we will try to solve another pain point of updating the HCM Extracts post-refresh with the FTP server details in TEST POD.

Normally any vendor/internal system will have two FTP servers one for TEST POD and another for PROD POD. With P2T refresh from PROD to TEST the production extract settings will be overridden in TEST POD. And we have to manually update the HCM Extracts and validate them else the extracts would fail.

There is a workaround for this issue if there is only one FTP for TEST and one for PROD. This workaround doesn’t work if there are multiple FTP servers with one extract pointing to one FTP and other extract to another FTP.

We can get the POD URL from the database table ASK_DEPLOYED_DOMAINS and we will use it to dynamically derive the FTP server based on the POD.

Table of Contents

SQL Query to get the POD Name:

SELECT UPPER(SUBSTR(EXTERNAL_VIRTUAL_HOST,1,instr(EXTERNAL_VIRTUAL_HOST,'.')-1)) INSTANCE_NAME
  FROM FUSION.ASK_DEPLOYED_DOMAINS
  WHERE DEPLOYED_DOMAIN_NAME='FADomain'

This query will provide output as ABCD-TEST.

Whatever parameters we pass on the HCM Extracts delivery options page will be passed to the BI Report using the Bursting query defined on the seeded globalReportsDataModel at /shared/Human Capital Management/Payroll/Data Models folder.

Now, if you want to implement this logic, we need to clone this data model and put it in /custom/ folder at whatever path is convenient for all Extracts and then customize the bursting SQL Query to add logic for ftp servers.

Seeded Bursting Query:

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
       ,pay_report_delivery.get_del_channel(del_opt.delivery_type,del_opt.ext_delivery_option_id,pact.payroll_action_id) 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

Modified Bursting Query:

WITH INSTANCE_DB
AS (
	SELECT UPPER(SUBSTR(EXTERNAL_VIRTUAL_HOST, 1, instr(EXTERNAL_VIRTUAL_HOST, '.') - 1)) INSTANCE_NAME
	FROM FUSION.ASK_DEPLOYED_DOMAINS
	WHERE DEPLOYED_DOMAIN_NAME = 'FADomain'
	)
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,
	pay_report_delivery.get_del_channel(del_opt.delivery_type, del_opt.ext_delivery_option_id, pact.payroll_action_id) DEL_CHANNEL
	/* Dynamic FTP server name for FTP Delivery Channel - For all other delivery channels, whatever is defined on the delivery options page will work */
	,
	(
		CASE 
			WHEN pay_report_delivery.get_del_channel(del_opt.delivery_type, del_opt.ext_delivery_option_id, pact.payroll_action_id) = 'FTP'
				THEN (
						CASE 
							WHEN INSTANCE_DB.INSTANCE_NAME = 'ABCD-TEST' /* Instance Name */
								THEN 'TEST_FTP_SERVER'
							ELSE 'PROD_FTP_SERVER'
							END
						)
			ELSE pay_report_delivery.get_del_param_value('1', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id)
			END
		) PARAMETER1
	/* Dynamic folder path for FTP - For all other delivery channels, whatever is defined on the delivery options page will work */
	,
	(
		CASE 
			WHEN pay_report_delivery.get_del_channel(del_opt.delivery_type, del_opt.ext_delivery_option_id, pact.payroll_action_id) = 'FTP'
				THEN (
						CASE 
							WHEN INSTANCE_DB.INSTANCE_NAME = 'ABCD-TEST' /* Instance Name */
								THEN '/E_1/Test/Archive/'
							ELSE '/E_1/PROD//Archive/'
							END
						)
			ELSE pay_report_delivery.get_del_param_value('2', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id)
			END
		) 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
	/* Dynamic file name for FTP - For all other delivery channels, whatever is defined on the delivery options page will work */
	,
	(
		CASE 
			WHEN pay_report_delivery.get_del_channel(del_opt.delivery_type, del_opt.ext_delivery_option_id, pact.payroll_action_id) = 'FTP'
				THEN (
						CASE 
							WHEN INSTANCE_DB.INSTANCE_NAME = 'ABCD-TEST' /* Instance Name */
								THEN 'CYE072WA_test_' || to_char(cast(sysdate AS TIMESTAMP), 'yyyymmddHH24MISS') || '.TXT'
							ELSE 'CYE072WA_' || to_char(cast(sysdate AS TIMESTAMP), 'yyyymmddHH24MISS') || '.TXT'
							END
						)
			ELSE pay_report_delivery.get_del_param_value('5', del_opt.ext_delivery_option_id, del_opt.delivery_type, NULL, 'PPA', pact.payroll_action_id)
			END
		) 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,
	INSTANCE_DB
WHERE pact.payroll_action_id = nvl(:archiveActionId, :payrollActionId)
	AND del_opt.ext_delivery_option_id = :deliveryOptionId

Once this custom Data Model has been built, we need to tag this custom data model for all the extracts in the place of seeded globalreports data model.

We need to implement this change in both TEST and PROD instances. Once this change is implemented, the logic will make sure to replace the FTP server dynamically post-refresh and we don’t need to do any changes post every P2T refresh.

NOTE: If there are multiple vendors and multiple FTP servers in each POD, then this functionality will not work. Please do your due diligence before using this functionality.

Hope this cool feature helps some clients if not all. If you have any questions/ know of any other ways to achieve it, please let me know in comments section.