In this article we will look into the queries to pull the Action Types, Actions, Action Reasons and Action Reason Usages.
Table of Contents
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.