SQL Query to fetch Delivery Option details for HCM Extracts

In this article, we will look into a query that can extract the Delivery Option details for HCM Extracts. It will be useful when migrating the HCM Extracts between environments to validate the successful migration and for documentation purposes as well.

SQL Query:

SELECT PEDT.DEFINITION_NAME,
	PEDT.DESCRIPTION,
	PEDOB.BASE_DELIVERY_OPTION_NAME,
	PEDOB.DATE_TO,
	PEDOB.DATE_FROM,
	PEDOB.DELIVERY_TYPE,
	PEDOB.BIP_REPORT_NAME,
	PEDOB.BIP_TEMPLATE_NAME,
	PEDOB.OUTPUT_TYPE,
	PEDOB.OUTPUT_NAME,
	PEDOB.OUTPUT_DIRECTORY_NAME,
	PEDOB.FILTER_NODE,
	PEDOB.BURSTING_NODE,
	PEDOB.LEGISLATION_CODE,
	PEDOB.MANDATORY_FLAG,
	PEDODP.OPTION_TYPE,
	PEDODP.OPTION_VALUE
FROM PER_EXT_DEFINITIONS_TL PEDT,
	PER_EXT_DELIVERY_OPTIONS_B PEDOB,
	PER_EXT_DELIVERY_OPTION_DTLS PEDODP
WHERE 1 = 1
	AND PEDT.EXT_DEFINITION_ID = PEDOB.EXT_DEFINITION_ID
	AND PEDOB.EXT_DELIVERY_OPTION_ID = PEDODP.EXT_DELIVERY_OPTION_ID
	AND sysdate between NVL(PEDOB.DATE_FROM,sysdate) and NVL(PEDOB.DATE_TO,sysdate)
	--and PEDT.DEFINITION_NAME LIKE 'HCM EXTRACT NAME%'

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.