Total Visits: 101
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.