Search This Blog

Saturday, April 7, 2012

SQL Query to extract Oracle Sales Order Workflow Status

Select WFA.DISPLAY_NAME      Process,
       WFA1.DISPLAY_NAME     Activity,       WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) Result,       LKP.MEANING           Act_status,
       WFS.NOTIFICATION_ID   Notif,       WFP.PROCESS_NAME      I_process,       WFP.ACTIVITY_NAME     I_activity,
       to_char(WFS.BEGIN_DATE,'DD-MON-RR_HH24:MI:SS') Begin_d,
       to_char(WFS.END_DATE,'DD-MON-RR_HH24:MI:SS')   End_d,
       WFS.ERROR_NAME        error_name
from WF_ITEM_ACTIVITY_STATUSES WFS,
     WF_PROCESS_ACTIVITIES     WFP,
     WF_ACTIVITIES_VL          WFA,
     WF_ACTIVITIES_VL          WFA1,
     WF_LOOKUPS                LKP
where 1=1
and  WFS.ITEM_TYPE          = 'OEOH'
and  WFS.item_key           = to_char(:v_header_id)
and  WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
and  WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
and  WFP.PROCESS_NAME       = WFA.NAME
and  WFP.PROCESS_VERSION    = WFA.VERSION
and  WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
and  WFP.ACTIVITY_NAME      = WFA1.NAME
and  WFA1.VERSION           = (select max(VERSION)
                              from WF_ACTIVITIES WF2
                              where WF2.ITEM_TYPE =            WFP.ACTIVITY_ITEM_TYPE
and   WF2.NAME              = WFP.ACTIVITY_NAME)
and  LKP.LOOKUP_TYPE        = 'WFENG_STATUS'
and  LKP.LOOKUP_CODE        = WFS.ACTIVITY_STATUS
order by WFS.ITEM_KEY,
         WFS.BEGIN_DATE,
         EXECUTION_TIME;

2 comments: