The blog provides the Oracle SQL Query to fetch the Workflow Error details in Oracle Apps R12
SQL Query to fetch the Item Type error details in Oracle Workflow
SELECT WF_ITEM_ACT_STATUS_TBL.ITEM_TYPE, WF_ITEM_ACT_STATUS_TBL.ITEM_KEY, WF_PROCESS_ACT_TBL.INSTANCE_LABEL, WF_PROCESS_ACT_TBL.PROCESS_NAME ACTIVITY, WF_ITEM_ACT_STATUS_TBL.ACTIVITY_RESULT_CODE, COUNT(*) FROM APPS.WF_ITEM_ACTIVITY_STATUSES WF_ITEM_ACT_STATUS_TBL, APPS.WF_PROCESS_ACTIVITIES WF_PROCESS_ACT_TBL WHERE 1 = 1 AND WF_ITEM_ACT_STATUS_TBL.ITEM_TYPE LIKE :ITEM_TYPE -- EG: REQAPPRV FOR REQUISITION APPROVAL AND WF_ITEM_ACT_STATUS_TBL.PROCESS_ACTIVITY = WF_PROCESS_ACT_TBL.INSTANCE_ID AND WF_ITEM_ACT_STATUS_TBL.ACTIVITY_STATUS = 'ERROR' GROUP BY WF_ITEM_ACT_STATUS_TBL.ITEM_TYPE, WF_ITEM_ACT_STATUS_TBL.ITEM_KEY, WF_PROCESS_ACT_TBL.INSTANCE_LABEL, WF_PROCESS_ACT_TBL.PROCESS_NAME, WF_ITEM_ACT_STATUS_TBL.ACTIVITY_RESULT_CODE ORDER BY WF_PROCESS_ACT_TBL.INSTANCE_LABEL;
SQL Query to fetch the Item Type error details in Oracle Workflow on a particular time period
SELECT WF_ITEM_ACT_STATUS_TBL.BEGIN_DATE, WF_ITEM_ACT_STATUS_TBL.ITEM_KEY, WF_Actvity_tbl.NAME ACTIVITY, WF_ITEM_ACT_STATUS_TBL.ACTIVITY_RESULT_CODE RESULT, WF_ITEM_ACT_STATUS_TBL.ERROR_NAME ERROR_NAME, WF_ITEM_ACT_STATUS_TBL.ERROR_MESSAGE ERROR_MESSAGE FROM WF_ITEM_ACTIVITY_STATUSES WF_ITEM_ACT_STATUS_TBL, WF_PROCESS_ACTIVITIES WF_Proc_Act_tbl, WF_ACTIVITIES WF_Actvity_tbl, WF_ACTIVITIES WF_Actvity_tbl_2, WF_ITEMS I WHERE 1=1 AND WF_ITEM_ACT_STATUS_TBL.ITEM_TYPE = 'REQAPPRV' AND WF_ITEM_ACT_STATUS_TBL.ACTIVITY_STATUS = 'ERROR' AND WF_ITEM_ACT_STATUS_TBL.PROCESS_ACTIVITY = WF_Proc_Act_tbl.INSTANCE_ID AND WF_Proc_Act_tbl.ACTIVITY_NAME = AC.NAME AND WF_Proc_Act_tbl.ACTIVITY_ITEM_TYPE = AC.ITEM_TYPE AND WF_Proc_Act_tbl.PROCESS_NAME = WF_Actvity_tbl_2.NAME AND WF_Proc_Act_tbl.PROCESS_ITEM_TYPE = WF_Actvity_tbl_2.ITEM_TYPE AND WF_Proc_Act_tbl.PROCESS_VERSION = WF_Actvity_tbl_2.VERSION AND I.ITEM_TYPE = 'REQAPPRV' AND I.ITEM_KEY = WF_ITEM_ACT_STATUS_TBL.ITEM_KEY AND I.BEGIN_DATE >= AC.BEGIN_DATE AND I.BEGIN_DATE < NVL (AC.END_DATE, I.BEGIN_DATE + 1) AND TRUNC(I.BEGIN_DATE) BETWEEN TO_DATE(:BEGIN_DATE) AND TRUNC(:END_DATE) ORDER BY WF_ITEM_ACT_STATUS_TBL.BEGIN_DATE DESC