Search This Blog

Saturday, April 7, 2012

SQL Query to extract oracle Sales order line Work flow status

select wfs.item_key             item_key,
       lin.line_number,   
       WFA.DISPLAY_NAME         PROCESS_NAME,
       WFA1.DISPLAY_NAME        ACTIVITY_NAME,
       WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT_CODE,
       LKP.MEANING                MEANING,
       WFS.NOTIFICATION_ID        NOTIFICATION_ID,
       WFP.PROCESS_NAME           INT_Process_name,
       WFP.ACTIVITY_NAME          INT_Activity_name,
       to_char(WFS.BEGIN_DATE,'DD-MON-RR_HH24:MI:SS') B_Date,
       to_char(WFS.END_DATE,'DD-MON-RR_HH24:MI:SS')   E_Date,
       WFS.ERROR_NAME             Err_name,
       WFS.BEGIN_DATE             BEGIN_DATE2,
       WFS.EXECUTION_TIME         EXECUTION_TIME2
from WF_ITEM_ACTIVITY_STATUSES WFS,
     WF_PROCESS_ACTIVITIES     WFP,
     WF_ACTIVITIES_VL          WFA,
     WF_ACTIVITIES_VL          WFA1,
     wf_lookups                lkp,
     (select oola.line_number,oola.line_id,oola.header_id
     from oe_order_lines_all oola
     where 1=1
     and oola.header_id = &header_id) lin
where 2=2
and   wfs.item_type          = 'OEOL'
  and  WFS.item_key         = to_char(lin.line_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 1, 13, 14;

1 comment: