SQL Queries to pull Action Type, Action, Action Reason and Action Reason Usages

In this article we will look into the queries to pull the Action Types, Actions, Action Reasons and Action Reason Usages.

SQL Query to pull Action Types:

SELECT B.ACTION_TYPE_ID,
	B.ACTION_TYPE_CODE,
	T.MEANING,
	B.BUSINESS_GROUP_ID,
	B.OBJECT_VERSION_NUMBER,
	B.CREATED_BY,
	B.CREATION_DATE,
	B.LAST_UPDATE_DATE,
	B.LAST_UPDATE_LOGIN,
	B.LAST_UPDATED_BY,
	B.MODULE_ID
FROM PER_ACTION_TYPES_B B,
	PER_ACTION_TYPES_TL T
WHERE B.ACTION_TYPE_ID = T.ACTION_TYPE_ID

SQL Query to pull Actions:

SELECT B.ACTION_ID,
	B.ACTION_CODE,
	B.ACTION_TYPE_CODE,
	T.DESCRIPTION,
	T.ACTION_NAME,
	B.START_DATE,
	B.END_DATE,
	B.ACTION_TYPE_ID,
	B.CREATED_BY,
	B.CREATION_DATE,
	B.LAST_UPDATE_DATE,
	B.LAST_UPDATED_BY
FROM PER_ACTIONS_B B,
	PER_ACTIONS_TL T
WHERE B.ACTION_ID = T.ACTION_ID

SQL Query to pull Action Reasons:

SELECT B.ACTION_REASON_ID,
	B.OBJECT_VERSION_NUMBER,
	B.BUSINESS_GROUP_ID,
	B.ACTION_REASON_CODE,
	T.ACTION_REASON,
	B.START_DATE,
	B.END_DATE,
	B.CREATED_BY,
	B.CREATION_DATE,
	B.LAST_UPDATE_DATE,
	B.LAST_UPDATED_BY
FROM PER_ACTION_REASONS_B B,
	PER_ACTION_REASONS_TL T
WHERE B.ACTION_REASON_ID = T.ACTION_REASON_ID

SQL Query to pull Action Reason Usages:

SELECT TYPE.ACTION_TYPE_CODE ACTIONTYPE,
	TYPE.MEANING ACTIONTYPEMEANING,
	ACTIONS.ACTION_CODE ACTIONCODE,
	ACTIONS.DESCRIPTION ACTIONDESCRIPTION,
	ACTIONS.START_DATE ACTIONSTDT,
	ACTIONS.END_DATE ACTIONENDT,
	REASON.ACTION_REASON_CODE REASONCODE,
	REASON.ACTION_REASON REASON,
	REASON.START_DATE REASONSTDT,
	REASON.END_DATE REASONENDT,
	USAGES.START_DATE USAGESSTDT,
	USAGES.END_DATE USAGESENDT,
	ACTIONS.MODULE_ID ACTIONMODULEID
FROM (
	SELECT B.ACTION_ID,
		B.ACTION_CODE,
		B.ACTION_TYPE_CODE,
		T.DESCRIPTION,
		T.ACTION_NAME,
		B.START_DATE,
		B.END_DATE,
		B.ACTION_TYPE_ID,
		B.CREATED_BY,
		B.CREATION_DATE,
		B.LAST_UPDATE_DATE,
		B.LAST_UPDATED_BY,
		B.MODULE_ID
	FROM PER_ACTIONS_B B,
		PER_ACTIONS_TL T
	WHERE B.ACTION_ID = T.ACTION_ID
	) ACTIONS,
	(
		SELECT B.ACTION_TYPE_ID,
			B.ACTION_TYPE_CODE,
			T.MEANING,
			B.BUSINESS_GROUP_ID,
			B.OBJECT_VERSION_NUMBER,
			B.CREATED_BY,
			B.CREATION_DATE,
			B.LAST_UPDATE_DATE,
			B.LAST_UPDATE_LOGIN,
			B.LAST_UPDATED_BY,
			B.MODULE_ID
		FROM PER_ACTION_TYPES_B B,
			PER_ACTION_TYPES_TL T
		WHERE B.ACTION_TYPE_ID = T.ACTION_TYPE_ID
		) TYPE,
	FUSION.PER_ACTION_REASON_USAGES USAGES,
	(
		SELECT B.ACTION_REASON_ID,
			B.OBJECT_VERSION_NUMBER,
			B.BUSINESS_GROUP_ID,
			B.ACTION_REASON_CODE,
			T.ACTION_REASON,
			B.START_DATE,
			B.END_DATE,
			B.CREATED_BY,
			B.CREATION_DATE,
			B.LAST_UPDATE_DATE,
			B.LAST_UPDATED_BY
		FROM PER_ACTION_REASONS_B B,
			PER_ACTION_REASONS_TL T
		WHERE B.ACTION_REASON_ID = T.ACTION_REASON_ID
		) REASON
WHERE ACTIONS.ACTION_TYPE_ID = TYPE.ACTION_TYPE_ID
	AND USAGES.ACTION_ID(+) = ACTIONS.ACTION_ID
	AND REASON.ACTION_REASON_ID(+) = USAGES.ACTION_REASON_ID
ORDER BY TYPE.ACTION_TYPE_CODE,
	ACTIONS.ACTION_CODE,
	REASON.ACTION_REASON_CODE

Hope it helps when you have similar requirements.

• Bio
• Facebook
• LinkedIn
• Latest Posts

Sricharan Monigari

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.

Latest posts by Sricharan Monigari (see all)

• Performance Management Overview Part-2 - January 19, 2021
• Performance Management Overview Part-1 - January 11, 2021
• How to use Data Disposal Functionality in HCM Cloud? - January 4, 2021
• How to load Global and Local Names using HDL? - December 28, 2020
• FREE Cloud based SQL Developer like tool for Oracle Cloud - December 21, 2020