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

 258 total views

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.