Query to get workflow details
•By admin
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
SQL Querysql
1SELECT *
2 FROM (SELECT workflowitemeo.item_type,
3 workflowitemeo.item_key,
4 workflowitemeo.root_activity,
5 activityeo.display_name process_name,
6 workflowitemeo.root_activity_version,
7 workflowitemeo.owner_role,
8 workflowitemeo.parent_item_type,
9 workflowitemeo.parent_item_key,
10 workflowitemeo.parent_context,
11 workflowitemeo.begin_date,
12 workflowitemeo.end_date,
13 workflowitemeo.user_key,
14 workflowitemtypeeo.name,
15 workflowitemtypeeo.display_name,
16 wf_directory.getroledisplayname2 (workflowitemeo.owner_role)
17 AS role_name,
18 wf_fwkmon.getitemstatus (workflowitemeo.item_type,
19 workflowitemeo.item_key,
20 workflowitemeo.end_date,
21 workflowitemeo.root_activity,
22 workflowitemeo.root_activity_version)
23 AS status_code,
24 wf_fwkmon.getroleemailaddress (workflowitemeo.owner_role)
25 AS role_email,
26 DECODE (
27 (SELECT COUNT (0)
28 FROM wf_items wi2
29 WHERE workflowitemeo.item_type = wi2.parent_item_type
30 AND workflowitemeo.item_key = wi2.parent_item_key),
31 0, 'WfMonNoChildren',
32 'WfMonChildrenExist')
33 AS child_switcher
34 FROM wf_items workflowitemeo,
35 wf_item_types_vl workflowitemtypeeo,
36 wf_activities_vl activityeo
37 WHERE workflowitemeo.item_type = workflowitemtypeeo.name
38 AND ActivityEO.ITEM_TYPE = WorkflowItemEO.ITEM_TYPE
39 AND ActivityEO.NAME = WorkflowItemEO.ROOT_ACTIVITY
40 AND activityeo.version = workflowitemeo.root_activity_version)
41 QRSLT
42 WHERE (item_type = :item_type AND item_key = :item_key);2. Query to get workflow activity history
SQL Querysql
1SELECT *
2 FROM (SELECT wias.item_type AS item_type,
3 wias.item_key AS item_key,
4 wias.process_activity AS process_activity,
5 wias.ROWID AS row_id,
6 'R' AS row_source,
7 wias.notification_id AS notif_id,
8 DECODE (wn.status,
9 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
10 wias.assigned_user)
11 AS assigned_user,
12 wias.activity_status AS activity_status,
13 wias.activity_result_code AS activity_result_code,
14 wias.execution_time AS execution_time,
15 wias.begin_date AS begin_date,
16 wias.end_date AS end_date,
17 wias.due_date AS due_date,
18 wl.meaning AS status_display,
19 wa.name AS activity_name,
20 wa.display_name AS activity_display,
21 wi.user_key AS user_key,
22 wa2.name AS parent_activity,
23 DECODE (wa2.name, 'ROOT', '', wa2.display_name)
24 AS parent_activity_display_name,
25 wa.TYPE AS activity_type,
26 wf_fwkmon.getroleemailaddress (
27 DECODE (
28 wn.status,
29 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
30 wias.assigned_user))
31 AS role_email_address,
32 wf_directory.getroledisplayname2 (
33 DECODE (
34 wn.status,
35 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
36 wias.assigned_user))
37 AS role_display_name,
38 DECODE (wias.activity_result_code,
39 '#NULL', 'WfNoCloseDate',
40 'WfCloseDate')
41 AS end_date_col_switch,
42 DECODE (wias.activity_status,
43 'ERROR', 'WfStatusErrorText',
44 'WfStatusNoterrText')
45 AS status_column_switch,
46 DECODE (wias.activity_status,
47 'ERROR', 'WfStatusError',
48 'COMPLETE', 'WfStatusComplete',
49 'SUSPEND', 'WfStatusSuspended',
50 'WAITING', 'WfStatusWaiting',
51 'DEFERRED', 'WfStatusDeferred',
52 'NOTIFIED', 'WfStatusNotified',
53 'WfStatusActive')
54 AS image_column_switch,
55 wf_core.activity_result (
56 wa.result_type,
57 DECODE (wias.activity_result_code,
58 '#NULL', NULL,
59 wias.activity_result_code))
60 AS result_display,
61 wpa.activity_item_type AS activity_item_type,
62 DECODE (
63 wa.TYPE,
64 'NOTICE', DECODE (wias.activity_status,
65 'NOTIFIED', 'WfReassignEnabled',
66 'ERROR', 'WfReassignEnabled',
67 'WfReassignDisabled'),
68 'WfReassignDisabled')
69 AS reassign_switcher,
70 DECODE (wias.activity_status,
71 'NOTIFIED', 'N',
72 'ACTIVE', 'N',
73 'ERROR', 'N',
74 'WAITING', 'N',
75 'DEFERRED', 'N',
76 'Y')
77 AS select_disabled,
78 DECODE (
79 wa.TYPE,
80 'PROCESS', DECODE (wias.activity_status,
81 'SUSPEND', 'WfResumeEnabled',
82 'COMPLETE', 'WfSuspResDisabled',
83 'WfSuspendEnabled'),
84 'WfSuspResDisabled')
85 AS suspend_switcher,
86 wa.expand_role AS expand_role,
87 DECODE (
88 wn.status,
89 'OPEN', NVL2 (
90 wn.more_info_role,
91 wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
92 wnl.meaning),
93 wnl.meaning)
94 AS notification_status
95 FROM wf_item_activity_statuses wias
96 LEFT JOIN wf_notifications wn
97 ON wias.notification_id = wn.notification_id
98 LEFT JOIN wf_lookups wnl
99 ON wnl.lookup_code = wn.status
100 AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
101 wf_lookups wl,
102 wf_items wi,
103 wf_activities_vl wa,
104 wf_process_activities wpa,
105 wf_activities_vl wa2
106 WHERE wl.lookup_code = wias.activity_status
107 AND wl.lookup_type = 'WFENG_STATUS'
108 AND wias.item_type = wi.item_type
109 AND wias.item_key = wi.item_key
110 AND wias.process_activity = wpa.instance_id
111 AND wpa.activity_name = wa.name
112 AND wpa.activity_item_type = wa.item_type
113 AND wi.begin_date BETWEEN wa.begin_date
114 AND NVL (wa.end_date, wi.begin_date)
115 AND wpa.process_name = wa2.name
116 AND wpa.process_item_type = wa2.item_type
117 AND wpa.process_version = wa2.version
118 AND wias.item_type = :item_type
119 AND wias.item_key = :item_key
120 UNION ALL
121 SELECT wiash.item_type AS item_type,
122 wiash.item_key AS item_key,
123 wiash.process_activity AS process_activity,
124 wiash.ROWID AS row_id,
125 'H' AS row_source,
126 wiash.notification_id AS notif_id,
127 DECODE (wn.status,
128 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
129 wiash.assigned_user)
130 AS assigned_user,
131 wiash.activity_status AS activity_status,
132 wiash.activity_result_code AS activity_result_code,
133 wiash.execution_time AS execution_time,
134 wiash.begin_date AS begin_date,
135 wiash.end_date AS end_date,
136 wiash.due_date AS due_date,
137 wl.meaning AS status_display,
138 wa.name AS activity_name,
139 wa.display_name AS activity_display,
140 wi.user_key AS user_key,
141 wa2.name AS parent_activity,
142 DECODE (wa2.name, 'ROOT', '', wa2.display_name)
143 AS parent_activity_display_name,
144 wa.TYPE AS activity_type,
145 wf_fwkmon.getroleemailaddress (
146 DECODE (
147 wn.status,
148 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
149 wiash.assigned_user))
150 AS role_email_address,
151 wf_directory.getroledisplayname2 (
152 DECODE (
153 wn.status,
154 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
155 wiash.assigned_user))
156 AS role_display_name,
157 DECODE (wiash.activity_result_code,
158 '#NULL', 'WfNoCloseDate',
159 'WfCloseDate')
160 AS end_date_col_switch,
161 DECODE (wiash.activity_status,
162 'ERROR', 'WfStatusErrorText',
163 'WfStatusNoterrText')
164 AS status_column_switch,
165 DECODE (wiash.activity_status,
166 'ERROR', 'WfStatusError',
167 'COMPLETE', 'WfStatusComplete',
168 'SUSPEND', 'WfStatusSuspended',
169 'WAITING', 'WfStatusWaiting',
170 'DEFERRED', 'WfStatusDeferred',
171 'NOTIFIED', 'WfStatusNotified',
172 'WfStatusActive')
173 AS image_column_switch,
174 wf_core.activity_result (
175 wa.result_type,
176 DECODE (wiash.activity_result_code,
177 '#NULL', NULL,
178 wiash.activity_result_code))
179 AS result_display,
180 wpa.activity_item_type AS activity_item_type,
181 DECODE (
182 wa.TYPE,
183 'NOTICE', DECODE (wiash.activity_status,
184 'NOTIFIED', 'WfReassignEnabled',
185 'ERROR', 'WfReassignEnabled',
186 'WfReassignDisabled'),
187 'WfReassignDisabled')
188 AS reassign_switcher,
189 DECODE (wiash.activity_status,
190 'NOTIFIED', 'N',
191 'ACTIVE', 'N',
192 'ERROR', 'N',
193 'WAITING', 'N',
194 'DEFERRED', 'N',
195 'Y')
196 AS select_disabled,
197 DECODE (
198 wa.TYPE,
199 'PROCESS', DECODE (wiash.activity_status,
200 'SUSPEND', 'WfResumeEnabled',
201 'COMPLETE', 'WfSuspResDisabled',
202 'WfSuspendEnabled'),
203 'WfSuspResDisabled')
204 AS suspend_switcher,
205 wa.expand_role AS expand_role,
206 DECODE (
207 wn.status,
208 'OPEN', NVL2 (
209 wn.more_info_role,
210 wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
211 wnl.meaning),
212 wnl.meaning)
213 AS notification_status
214 FROM wf_item_activity_statuses_h wiash
215 LEFT JOIN wf_notifications wn
216 ON wiash.notification_id = wn.notification_id
217 LEFT JOIN wf_lookups wnl
218 ON wnl.lookup_code = wn.status
219 AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
220 wf_lookups wl,
221 wf_items wi,
222 wf_activities_vl wa,
223 wf_process_activities wpa,
224 wf_activities_vl wa2
225 WHERE wl.lookup_code = wiash.activity_status
226 AND wl.lookup_type = 'WFENG_STATUS'
227 AND wiash.item_type = wi.item_type
228 AND wiash.item_key = wi.item_key
229 AND wiash.process_activity = wpa.instance_id
230 AND wpa.activity_name = wa.name
231 AND wpa.activity_item_type = wa.item_type
232 AND wi.begin_date BETWEEN wa.begin_date
233 AND NVL (wa.end_date, wi.begin_date)
234 AND wpa.process_name = wa2.name
235 AND wpa.process_item_type = wa2.item_type
236 AND wpa.process_version = wa2.version
237 AND wiash.item_type = :item_type
238 AND wiash.item_key = :item_key) qrslt
239ORDER BY 11 DESC, 10 DESCIf you have read only access to apps user objects, please use below query.
SQL Querysql
1SELECT (SELECT Invoice_num
2 FROM apps.ap_invoices_all apinv
3 WHERE SUBSTR (qrslt.ITEM_KEY, 1, 7) = apinv.invoice_id)
4 invoice_num,
5 qrslt.*
6 FROM (SELECT wias.item_type AS item_type,
7 wias.item_key AS item_key,
8 wias.process_activity AS process_activity,
9 wias.ROWID AS row_id,
10 'R' AS row_source,
11 wias.notification_id AS notif_id,
12 DECODE (wn.status,
13 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
14 wias.assigned_user)
15 AS assigned_user,
16 wias.activity_status AS activity_status,
17 wias.activity_result_code AS activity_result_code,
18 wias.execution_time AS execution_time,
19 wias.begin_date AS begin_date,
20 wias.end_date AS end_date,
21 wias.due_date AS due_date,
22 wl.meaning AS status_display,
23 wa.name AS activity_name,
24 wa.display_name AS activity_display,
25 wi.user_key AS user_key,
26 wa2.name AS parent_activity,
27 DECODE (wa2.name, 'ROOT', '', wa2.display_name)
28 AS parent_activity_display_name,
29 wa.TYPE AS activity_type,
30 -- wf_fwkmon.getroleemailaddress (
31 -- DECODE (
32 -- wn.status,
33 -- 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
34 -- wias.assigned_user))
35 wias.assigned_user AS role_email_address,
36 -- wf_directory.getroledisplayname2 (
37 -- DECODE (
38 -- wn.status,
39 -- 'OPEN', NVL (wn.more_info_role, wias.assigned_user),
40 -- wias.assigned_user))
41 wias.assigned_user AS role_display_name,
42 DECODE (wias.activity_result_code,
43 '#NULL', 'WfNoCloseDate',
44 'WfCloseDate')
45 AS end_date_col_switch,
46 DECODE (wias.activity_status,
47 'ERROR', 'WfStatusErrorText',
48 'WfStatusNoterrText')
49 AS status_column_switch,
50 DECODE (wias.activity_status,
51 'ERROR', 'WfStatusError',
52 'COMPLETE', 'WfStatusComplete',
53 'SUSPEND', 'WfStatusSuspended',
54 'WAITING', 'WfStatusWaiting',
55 'DEFERRED', 'WfStatusDeferred',
56 'NOTIFIED', 'WfStatusNotified',
57 'WfStatusActive')
58 AS image_column_switch,
59 -- wf_core.activity_result (
60 -- wa.result_type,
61 -- DECODE (wias.activity_result_code,
62 -- '#NULL', NULL,
63 -- wias.activity_result_code))
64 wias.activity_result_code AS result_display,
65 wpa.activity_item_type AS activity_item_type,
66 DECODE (
67 wa.TYPE,
68 'NOTICE', DECODE (wias.activity_status,
69 'NOTIFIED', 'WfReassignEnabled',
70 'ERROR', 'WfReassignEnabled',
71 'WfReassignDisabled'),
72 'WfReassignDisabled')
73 AS reassign_switcher,
74 DECODE (wias.activity_status,
75 'NOTIFIED', 'N',
76 'ACTIVE', 'N',
77 'ERROR', 'N',
78 'WAITING', 'N',
79 'DEFERRED', 'N',
80 'Y')
81 AS select_disabled,
82 DECODE (
83 wa.TYPE,
84 'PROCESS', DECODE (wias.activity_status,
85 'SUSPEND', 'WfResumeEnabled',
86 'COMPLETE', 'WfSuspResDisabled',
87 'WfSuspendEnabled'),
88 'WfSuspResDisabled')
89 AS suspend_switcher,
90 wa.expand_role AS expand_role,
91 -- DECODE (
92 -- wn.status,
93 -- 'OPEN', NVL2 (
94 -- wn.more_info_role,
95 -- wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
96 -- wnl.meaning),
97 -- wnl.meaning)
98 wnl.meaning AS notification_status
99 FROM apps.wf_item_activity_statuses wias
100 LEFT JOIN apps.wf_notifications wn
101 ON wias.notification_id = wn.notification_id
102 LEFT JOIN apps.wf_lookups wnl
103 ON wnl.lookup_code = wn.status
104 AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
105 apps.wf_lookups wl,
106 apps.wf_items wi,
107 apps.wf_activities_vl wa,
108 apps.wf_process_activities wpa,
109 apps.wf_activities_vl wa2
110 WHERE wl.lookup_code = wias.activity_status
111 AND wl.lookup_type = 'WFENG_STATUS'
112 AND wias.item_type = wi.item_type
113 AND wias.item_key = wi.item_key
114 AND wias.process_activity = wpa.instance_id
115 AND wpa.activity_name = wa.name
116 AND wpa.activity_item_type = wa.item_type
117 AND wi.begin_date BETWEEN wa.begin_date
118 AND NVL (wa.end_date, wi.begin_date)
119 AND wpa.process_name = wa2.name
120 AND wpa.process_item_type = wa2.item_type
121 AND wpa.process_version = wa2.version
122 AND wias.item_type = :item_type
123 -- AND wias.item_key = :item_key
124 UNION ALL
125 SELECT wiash.item_type AS item_type,
126 wiash.item_key AS item_key,
127 wiash.process_activity AS process_activity,
128 wiash.ROWID AS row_id,
129 'H' AS row_source,
130 wiash.notification_id AS notif_id,
131 DECODE (wn.status,
132 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
133 wiash.assigned_user)
134 AS assigned_user,
135 wiash.activity_status AS activity_status,
136 wiash.activity_result_code AS activity_result_code,
137 wiash.execution_time AS execution_time,
138 wiash.begin_date AS begin_date,
139 wiash.end_date AS end_date,
140 wiash.due_date AS due_date,
141 wl.meaning AS status_display,
142 wa.name AS activity_name,
143 wa.display_name AS activity_display,
144 wi.user_key AS user_key,
145 wa2.name AS parent_activity,
146 DECODE (wa2.name, 'ROOT', '', wa2.display_name)
147 AS parent_activity_display_name,
148 wa.TYPE AS activity_type,
149 -- wf_fwkmon.getroleemailaddress (
150 -- DECODE (
151 -- wn.status,
152 -- 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
153 -- wiash.assigned_user))
154 wiash.assigned_user AS role_email_address,
155 -- wf_directory.getroledisplayname2 (
156 -- DECODE (
157 -- wn.status,
158 -- 'OPEN', NVL (wn.more_info_role, wiash.assigned_user),
159 -- wiash.assigned_user))
160 wiash.assigned_user AS role_display_name,
161 DECODE (wiash.activity_result_code,
162 '#NULL', 'WfNoCloseDate',
163 'WfCloseDate')
164 AS end_date_col_switch,
165 DECODE (wiash.activity_status,
166 'ERROR', 'WfStatusErrorText',
167 'WfStatusNoterrText')
168 AS status_column_switch,
169 DECODE (wiash.activity_status,
170 'ERROR', 'WfStatusError',
171 'COMPLETE', 'WfStatusComplete',
172 'SUSPEND', 'WfStatusSuspended',
173 'WAITING', 'WfStatusWaiting',
174 'DEFERRED', 'WfStatusDeferred',
175 'NOTIFIED', 'WfStatusNotified',
176 'WfStatusActive')
177 AS image_column_switch,
178 -- wf_core.activity_result (
179 -- wa.result_type,
180 -- DECODE (wiash.activity_result_code,
181 -- '#NULL', NULL,
182 -- wiash.activity_result_code))
183 wiash.activity_result_code AS result_display,
184 wpa.activity_item_type AS activity_item_type,
185 DECODE (
186 wa.TYPE,
187 'NOTICE', DECODE (wiash.activity_status,
188 'NOTIFIED', 'WfReassignEnabled',
189 'ERROR', 'WfReassignEnabled',
190 'WfReassignDisabled'),
191 'WfReassignDisabled')
192 AS reassign_switcher,
193 DECODE (wiash.activity_status,
194 'NOTIFIED', 'N',
195 'ACTIVE', 'N',
196 'ERROR', 'N',
197 'WAITING', 'N',
198 'DEFERRED', 'N',
199 'Y')
200 AS select_disabled,
201 DECODE (
202 wa.TYPE,
203 'PROCESS', DECODE (wiash.activity_status,
204 'SUSPEND', 'WfResumeEnabled',
205 'COMPLETE', 'WfSuspResDisabled',
206 'WfSuspendEnabled'),
207 'WfSuspResDisabled')
208 AS suspend_switcher,
209 wa.expand_role AS expand_role,
210 -- DECODE (
211 -- wn.status,
212 -- 'OPEN', NVL2 (
213 -- wn.more_info_role,
214 -- wf_core.TRANSLATE ('WFNTF_MOREINFO_REQUESTED'),
215 -- wnl.meaning),
216 -- wnl.meaning)
217 wnl.meaning AS notification_status
218 FROM apps.wf_item_activity_statuses_h wiash
219 LEFT JOIN apps.wf_notifications wn
220 ON wiash.notification_id = wn.notification_id
221 LEFT JOIN apps.wf_lookups wnl
222 ON wnl.lookup_code = wn.status
223 AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
224 apps.wf_lookups wl,
225 apps.wf_items wi,
226 apps.wf_activities_vl wa,
227 apps.wf_process_activities wpa,
228 apps.wf_activities_vl wa2
229 WHERE wl.lookup_code = wiash.activity_status
230 AND wl.lookup_type = 'WFENG_STATUS'
231 AND wiash.item_type = wi.item_type
232 AND wiash.item_key = wi.item_key
233 AND wiash.process_activity = wpa.instance_id
234 AND wpa.activity_name = wa.name
235 AND wpa.activity_item_type = wa.item_type
236 AND wi.begin_date BETWEEN wa.begin_date
237 AND NVL (wa.end_date, wi.begin_date)
238 AND wpa.process_name = wa2.name
239 AND wpa.process_item_type = wa2.item_type
240 AND wpa.process_version = wa2.version
241 AND wiash.item_type = :item_type
242 -- AND wiash.item_key = :item_key
243 ) qrslt
244 WHERE 1 = 1
245 ----begin_date >sysdate-30
246ORDER BY 11 DESC, 10 DESC