Wednesday, 19 August 2015

Workflow: SQL Query to get workflow notification errors within a certain period

                                         The below SQL query is used to get workflow notification errors within a certain period for a certain Item_type, you can replace the item_type with yours in the below query.

SELECT   ias.begin_date, ias.item_key, ac.NAME activity,
         ias.activity_result_code RESULT, ias.error_name error_name,
         ias.error_message error_message
    FROM wf_item_activity_statuses ias,
         wf_process_activities pa,
         wf_activities ac,
         wf_activities ap,
         wf_items i
   WHERE ias.item_type = 'HRSSA'
     AND ias.activity_status = 'ERROR'
     AND ias.process_activity = pa.instance_id
     AND pa.activity_name = ac.NAME
     AND pa.activity_item_type = ac.item_type
     AND pa.process_name = ap.NAME
     AND pa.process_item_type = ap.item_type
     AND pa.process_version = ap.VERSION
     AND i.item_type = 'HRSSA'
     AND i.item_key = ias.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 ias.begin_date DESC;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.