• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:September 22, 2020
  • Reading time:3 mins read
SQL Queries to pull Action Type, Action, Action Reason and Action Reason Usages
SQL Queries to pull Action Type, Action, Action Reason and Action Reason Usages
- SQL Queries to pull Action Type, Action, Action Reason and Action Reason Usages
Total Visits: 39

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.