Query to get workflow details in oracle apps
SQL to get workflow activity history
Backend query for workflow
1. Query to get workflow item details
SELECT *
FROM (SELECT workflowitemeo.item_type,
workflowitemeo.item_key,
workflowitemeo.root_activity,
activityeo.display_name process_name,
workflowitemeo.root_activity_version,
workflowitemeo.owner_role,
workflowitemeo.parent_item_type,
workflowitemeo.parent_item_key,
workflowitemeo.parent_context,
workflowitemeo.begin_date,
workflowitemeo.end_date,
workflowitemeo.user_key,
workflowitemtypeeo.name,
workflowitemtypeeo.display_name,
wf_directory.getroledisplayname2 (workflowitemeo.owner_role)
AS role_name,
wf_fwkmon.getitemstatus (workflowitemeo.item_type,
workflowitemeo.item_key,
workflowitemeo.end_date,
workflowitemeo.root_activity,
workflowitemeo.root_activity_version)
AS status_code,
wf_fwkmon.getroleemailaddress (workflowitemeo.owner_role)
AS role_email,
DECODE (
(SELECT COUNT (0)
FROM wf_items wi2
WHERE workflowitemeo.item_type = wi2.parent_item_type
AND workflowitemeo.item_key = wi2.parent_item_key),
0, 'WfMonNoChildren',
'WfMonChildrenExist')
AS child_switcher
FROM wf_items workflowitemeo,
wf_item_types_vl workflowitemtypeeo,
wf_activities_vl activityeo
WHERE workflowitemeo.item_type = workflowitemtypeeo.name
AND ActivityEO.ITEM_TYPE = WorkflowItemEO.ITEM_TYPE
AND ActivityEO.NAME = WorkflowItemEO.ROOT_ACTIVITY
AND activityeo.version = workflowitemeo.root_activity_version)
QRSLT
WHERE (item_type = :item_type AND item_key = :item_key);
2. Query to get workflow activity history
SELECT *
FROM (SELECT wias.item_type AS item_type,
wias.item_key AS item_key,
wias.process_activity AS process_activity,
wias.ROWID AS row_id,
'R' AS row_source,
wias.notification_id AS notif_id,
DECODE (wn.status,
'OPEN', NVL (wn.more_info_role, wias.assigned_user),
wias.assigned_user)
AS assigned_user,
wias.activity_status AS activity_status,
wias.activity_result_code AS activity_result_code,
wias.execution_time AS execution_time,
wias.begin_date AS begin_date,
wias.end_date AS end_date,
wias.due_date AS due_date,
wl.meaning AS status_display,
wa.name AS activity_name,
wa.display_name AS activity_display,
wi.user_key AS user_key,
wa2.name AS parent_activity,
DECODE (wa2.name, 'ROOT', '', wa2.display_name)
AS parent_activity_display_name,
wa.TYPE AS activity_type,
wf_fwkmon.getroleemailaddress (
DECODE (
wn.status,
'OPEN', NVL (wn.more_info_role, wias.assigned_user),
wias.assigned_user))
AS role_email_address,
wf_directory.getroledisplayname2 (
DECODE (
wn.status,
'OPEN', NVL (wn.more_info_role, wias.assigned_user),
wias.assigned_user))
AS role_display_name,
DECODE (wias.activity_result_code,
'#NULL', 'WfNoCloseDate',
'WfCloseDate')
AS end_date_col_switch,
DECODE (wias.activity_status,
'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText')
AS status_column_switch,
DECODE (wias.activity_status,
'ERROR', 'WfStatusError',
'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended',
'WAITING', 'WfStatusWaiting',
'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified',
'WfStatusActive')
AS image_column_switch,
wf_core.activity_result (
wa.result_type,
DECODE (wias.activity_result_code,
'#NULL', NULL,
wias.activity_result_code))
AS result_display,
wpa.activity_item_type AS activity_item_type,
DECODE (
wa.TYPE,
'NOTICE', DECODE (wias.activity_status,
'NOTIFIED', 'WfReassignEnabled',
'ERROR', 'WfReassignEnabled',
'WfReassignDisabled'),
'WfReassignDisabled')
AS reassign_switcher,
DECODE (wias.activity_status,
'NOTIFIED', 'N',
'ACTIVE', 'N',
'ERROR', 'N',
'WAITING', 'N',
'DEFERRED', 'N',
'Y')
AS select_disabled,
DECODE (
wa.TYPE,
'PROCESS', DECODE (wias.activity_status,
'SUSPEND', 'WfResumeEnabled',
'COMPLETE', 'WfSuspResDisabled',
'WfSuspendEnabled'),
'WfSuspResDisabled')
AS suspend_switcher,
wa.expand_role AS expand_role,
DECODE (
wn.status,
'OPEN', NVL2 (
wn.more_info_role,
wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
wnl.meaning),
wnl.meaning)
AS notification_status
FROM wf_item_activity_statuses wias
LEFT JOIN wf_notifications wn
ON wias.notification_id = wn.notification_id
LEFT JOIN wf_lookups wnl
ON wnl.lookup_code = wn.status
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wias.activity_status
AND wl.lookup_type = 'WFENG_STATUS'
AND wias.item_type = wi.item_type
AND wias.item_key = wi.item_key
AND wias.process_activity = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date
AND NVL (wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wias.item_type = :item_type
AND wias.item_key = :item_key
UNION ALL
SELECT wiash.item_type AS item_type,
wiash.item_key AS item_key,
wiash.process_activity AS process_activity,
wiash.ROWID AS row_id,
'H' AS row_source,
wiash.notification_id AS notif_id,
DECODE (wn.status,
'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
wiash.assigned_user)
AS assigned_user,
wiash.activity_status AS activity_status,
wiash.activity_result_code AS activity_result_code,
wiash.execution_time AS execution_time,
wiash.begin_date AS begin_date,
wiash.end_date AS end_date,
wiash.due_date AS due_date,
wl.meaning AS status_display,
wa.name AS activity_name,
wa.display_name AS activity_display,
wi.user_key AS user_key,
wa2.name AS parent_activity,
DECODE (wa2.name, 'ROOT', '', wa2.display_name)
AS parent_activity_display_name,
wa.TYPE AS activity_type,
wf_fwkmon.getroleemailaddress (
DECODE (
wn.status,
'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
wiash.assigned_user))
AS role_email_address,
wf_directory.getroledisplayname2 (
DECODE (
wn.status,
'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
wiash.assigned_user))
AS role_display_name,
DECODE (wiash.activity_result_code,
'#NULL', 'WfNoCloseDate',
'WfCloseDate')
AS end_date_col_switch,
DECODE (wiash.activity_status,
'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText')
AS status_column_switch,
DECODE (wiash.activity_status,
'ERROR', 'WfStatusError',
'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended',
'WAITING', 'WfStatusWaiting',
'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified',
'WfStatusActive')
AS image_column_switch,
wf_core.activity_result (
wa.result_type,
DECODE (wiash.activity_result_code,
'#NULL', NULL,
wiash.activity_result_code))
AS result_display,
wpa.activity_item_type AS activity_item_type,
DECODE (
wa.TYPE,
'NOTICE', DECODE (wiash.activity_status,
'NOTIFIED', 'WfReassignEnabled',
'ERROR', 'WfReassignEnabled',
'WfReassignDisabled'),
'WfReassignDisabled')
AS reassign_switcher,
DECODE (wiash.activity_status,
'NOTIFIED', 'N',
'ACTIVE', 'N',
'ERROR', 'N',
'WAITING', 'N',
'DEFERRED', 'N',
'Y')
AS select_disabled,
DECODE (
wa.TYPE,
'PROCESS', DECODE (wiash.activity_status,
'SUSPEND', 'WfResumeEnabled',
'COMPLETE', 'WfSuspResDisabled',
'WfSuspendEnabled'),
'WfSuspResDisabled')
AS suspend_switcher,
wa.expand_role AS expand_role,
DECODE (
wn.status,
'OPEN', NVL2 (
wn.more_info_role,
wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
wnl.meaning),
wnl.meaning)
AS notification_status
FROM wf_item_activity_statuses_h wiash
LEFT JOIN wf_notifications wn
ON wiash.notification_id = wn.notification_id
LEFT JOIN wf_lookups wnl
ON wnl.lookup_code = wn.status
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wiash.activity_status
AND wl.lookup_type = 'WFENG_STATUS'
AND wiash.item_type = wi.item_type
AND wiash.item_key = wi.item_key
AND wiash.process_activity = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date
AND NVL (wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wiash.item_type = :item_type
AND wiash.item_key = :item_key) qrslt
ORDER BY 11 DESC, 10 DESC
If you have read only access to apps user objects, please use below query.
SELECT (SELECT Invoice_num
FROM apps.ap_invoices_all apinv
WHERE SUBSTR (qrslt.ITEM_KEY, 1, 7) = apinv.invoice_id)
invoice_num,
qrslt.*
FROM (SELECT wias.item_type AS item_type,
wias.item_key AS item_key,
wias.process_activity AS process_activity,
wias.ROWID AS row_id,
'R' AS row_source,
wias.notification_id AS notif_id,
DECODE (wn.status,
'OPEN', NVL (wn.more_info_role, wias.assigned_user),
wias.assigned_user)
AS assigned_user,
wias.activity_status AS activity_status,
wias.activity_result_code AS activity_result_code,
wias.execution_time AS execution_time,
wias.begin_date AS begin_date,
wias.end_date AS end_date,
wias.due_date AS due_date,
wl.meaning AS status_display,
wa.name AS activity_name,
wa.display_name AS activity_display,
wi.user_key AS user_key,
wa2.name AS parent_activity,
DECODE (wa2.name, 'ROOT', '', wa2.display_name)
AS parent_activity_display_name,
wa.TYPE AS activity_type,
-- wf_fwkmon.getroleemailaddress (
-- DECODE (
-- wn.status,
-- 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
-- wias.assigned_user))
wias.assigned_user AS role_email_address,
-- wf_directory.getroledisplayname2 (
-- DECODE (
-- wn.status,
-- 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
-- wias.assigned_user))
wias.assigned_user AS role_display_name,
DECODE (wias.activity_result_code,
'#NULL', 'WfNoCloseDate',
'WfCloseDate')
AS end_date_col_switch,
DECODE (wias.activity_status,
'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText')
AS status_column_switch,
DECODE (wias.activity_status,
'ERROR', 'WfStatusError',
'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended',
'WAITING', 'WfStatusWaiting',
'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified',
'WfStatusActive')
AS image_column_switch,
-- wf_core.activity_result (
-- wa.result_type,
-- DECODE (wias.activity_result_code,
-- '#NULL', NULL,
-- wias.activity_result_code))
wias.activity_result_code AS result_display,
wpa.activity_item_type AS activity_item_type,
DECODE (
wa.TYPE,
'NOTICE', DECODE (wias.activity_status,
'NOTIFIED', 'WfReassignEnabled',
'ERROR', 'WfReassignEnabled',
'WfReassignDisabled'),
'WfReassignDisabled')
AS reassign_switcher,
DECODE (wias.activity_status,
'NOTIFIED', 'N',
'ACTIVE', 'N',
'ERROR', 'N',
'WAITING', 'N',
'DEFERRED', 'N',
'Y')
AS select_disabled,
DECODE (
wa.TYPE,
'PROCESS', DECODE (wias.activity_status,
'SUSPEND', 'WfResumeEnabled',
'COMPLETE', 'WfSuspResDisabled',
'WfSuspendEnabled'),
'WfSuspResDisabled')
AS suspend_switcher,
wa.expand_role AS expand_role,
-- DECODE (
-- wn.status,
-- 'OPEN', NVL2 (
-- wn.more_info_role,
-- wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
-- wnl.meaning),
-- wnl.meaning)
wnl.meaning AS notification_status
FROM apps.wf_item_activity_statuses wias
LEFT JOIN apps.wf_notifications wn
ON wias.notification_id = wn.notification_id
LEFT JOIN apps.wf_lookups wnl
ON wnl.lookup_code = wn.status
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
apps.wf_lookups wl,
apps.wf_items wi,
apps.wf_activities_vl wa,
apps.wf_process_activities wpa,
apps.wf_activities_vl wa2
WHERE wl.lookup_code = wias.activity_status
AND wl.lookup_type = 'WFENG_STATUS'
AND wias.item_type = wi.item_type
AND wias.item_key = wi.item_key
AND wias.process_activity = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date
AND NVL (wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wias.item_type = :item_type
-- AND wias.item_key = :item_key
UNION ALL
SELECT wiash.item_type AS item_type,
wiash.item_key AS item_key,
wiash.process_activity AS process_activity,
wiash.ROWID AS row_id,
'H' AS row_source,
wiash.notification_id AS notif_id,
DECODE (wn.status,
'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
wiash.assigned_user)
AS assigned_user,
wiash.activity_status AS activity_status,
wiash.activity_result_code AS activity_result_code,
wiash.execution_time AS execution_time,
wiash.begin_date AS begin_date,
wiash.end_date AS end_date,
wiash.due_date AS due_date,
wl.meaning AS status_display,
wa.name AS activity_name,
wa.display_name AS activity_display,
wi.user_key AS user_key,
wa2.name AS parent_activity,
DECODE (wa2.name, 'ROOT', '', wa2.display_name)
AS parent_activity_display_name,
wa.TYPE AS activity_type,
-- wf_fwkmon.getroleemailaddress (
-- DECODE (
-- wn.status,
-- 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
-- wiash.assigned_user))
wiash.assigned_user AS role_email_address,
-- wf_directory.getroledisplayname2 (
-- DECODE (
-- wn.status,
-- 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
-- wiash.assigned_user))
wiash.assigned_user AS role_display_name,
DECODE (wiash.activity_result_code,
'#NULL', 'WfNoCloseDate',
'WfCloseDate')
AS end_date_col_switch,
DECODE (wiash.activity_status,
'ERROR', 'WfStatusErrorText',
'WfStatusNoterrText')
AS status_column_switch,
DECODE (wiash.activity_status,
'ERROR', 'WfStatusError',
'COMPLETE', 'WfStatusComplete',
'SUSPEND', 'WfStatusSuspended',
'WAITING', 'WfStatusWaiting',
'DEFERRED', 'WfStatusDeferred',
'NOTIFIED', 'WfStatusNotified',
'WfStatusActive')
AS image_column_switch,
-- wf_core.activity_result (
-- wa.result_type,
-- DECODE (wiash.activity_result_code,
-- '#NULL', NULL,
-- wiash.activity_result_code))
wiash.activity_result_code AS result_display,
wpa.activity_item_type AS activity_item_type,
DECODE (
wa.TYPE,
'NOTICE', DECODE (wiash.activity_status,
'NOTIFIED', 'WfReassignEnabled',
'ERROR', 'WfReassignEnabled',
'WfReassignDisabled'),
'WfReassignDisabled')
AS reassign_switcher,
DECODE (wiash.activity_status,
'NOTIFIED', 'N',
'ACTIVE', 'N',
'ERROR', 'N',
'WAITING', 'N',
'DEFERRED', 'N',
'Y')
AS select_disabled,
DECODE (
wa.TYPE,
'PROCESS', DECODE (wiash.activity_status,
'SUSPEND', 'WfResumeEnabled',
'COMPLETE', 'WfSuspResDisabled',
'WfSuspendEnabled'),
'WfSuspResDisabled')
AS suspend_switcher,
wa.expand_role AS expand_role,
-- DECODE (
-- wn.status,
-- 'OPEN', NVL2 (
-- wn.more_info_role,
-- wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
-- wnl.meaning),
-- wnl.meaning)
wnl.meaning AS notification_status
FROM apps.wf_item_activity_statuses_h wiash
LEFT JOIN apps.wf_notifications wn
ON wiash.notification_id = wn.notification_id
LEFT JOIN apps.wf_lookups wnl
ON wnl.lookup_code = wn.status
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
apps.wf_lookups wl,
apps.wf_items wi,
apps.wf_activities_vl wa,
apps.wf_process_activities wpa,
apps.wf_activities_vl wa2
WHERE wl.lookup_code = wiash.activity_status
AND wl.lookup_type = 'WFENG_STATUS'
AND wiash.item_type = wi.item_type
AND wiash.item_key = wi.item_key
AND wiash.process_activity = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date
AND NVL (wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND wpa.process_version = wa2.version
AND wiash.item_type = :item_type
-- AND wiash.item_key = :item_key
) qrslt
WHERE 1 = 1
----begin_date >sysdate-30
ORDER BY 11 DESC, 10 DESC
Related posts:
Upload your own post and refer it anywhere anytime: