AgilePoint 流程管理常用SQL

1. 如何清除測試機的所有資料

delete WF_EVENTS

delete WF_AUTO_WORKITEMS

delete WF_MANUAL_WORKITEMS

delete WF_ACTIVITY_INSTS

delete WF_CUSTOM_ATTRS

delete WF_PROC_TRACKINGS

delete WF_LARGE_TEXTS where TEXT_ID in ( select PROC_INST_ID from WF_PROC_INSTS )

delete WF_PROC_INSTS

delete WF_MAIL_DELIVERABLES

2.取得指定流程資訊

select WK.WORK_ITEM_ID,WAI.NAME,WK.SESSION_, WK.STATUS,
       WK.ORIGINAL_USER_ID,O_WRU.FULL_NAME,
       WK.USER_ID,WRU.FULL_NAME,
       WK.ASSIGNED_DATE,WK.COMPLETED_DATE,WK.RESTRICTION_TYPE,WK.CLIENT_DATA
from dbo.WF_PROC_INSTS PIS,
     dbo.WF_ACTIVITY_INSTS WAI,
     dbo.WF_MANUAL_WORKITEMS WK  LEFT JOIN WF_REG_USERS WRU
                                     on(WK.USER_ID=WRU.USER_NAME)
                                 LEFT JOIN WF_REG_USERS O_WRU
                                     on(WK.USER_ID=O_WRU.USER_NAME)
where PIS.PROC_INST_NAME='通用表單-2009/12/1 10:51:30(791)'
  and PIS.PROC_INST_ID=WK.PROC_INST_ID
  and WK.ACTIVITY_INST_ID=WAI.ID
order by WK.ASSIGNED_DATE,WAI.NAME,WK.SESSION_

3.取得指定流程資訊（含組織)

以下SQL 可取得每一個WorkItem 及其相關組織的資訊, 但若人員有兼職情形, 因兼職資訊存在於ClientData中, 無法直接以SQL Join查詢, 因此會有一個WorkItem 多筆資料的情形,需自行刪除未使用到的部門

select WK.WORK_ITEM_ID,WAI.NAME,WK.SESSION_, WK.STATUS,

             WK.ORIGINAL_USER_ID,O_WRU.FULL_NAME,

            O_AUI.RANK as User_RANK,O_ADU.DEP_ID,O_ADI.DEP_NAME,

            O_ADI.DEP_MANAGER_NAME,O_ADI.MANAGER_LEVEL,O_ADI.UPPER_DEP_ID,

            WK.USER_ID,WRU.FULL_NAME,

            AUI.RANK as User_RANK,ADU.DEP_ID,ADI.DEP_NAME,

           ADI.DEP_MANAGER_NAME, ADI.MANAGER_LEVEL,ADI.UPPER_DEP_ID,

          WK.ASSIGNED_DATE,WK.COMPLETED_DATE,

         WK.RESTRICTION_TYPE,WK.CLIENT_DATA

from  dbo.WF_PROC_INSTS PIS,  dbo.WF_ACTIVITY_INSTS WAI,

           dbo.WF_MANUAL_WORKITEMS WK

                       LEFT JOIN WF_REG_USERS WRU

                                                on(WK.USER_ID=WRU.USER_NAME)

                       LEFT JOIN AF_USER_INFO AUI

                                              on(WK.USER_ID=AUI.USER_NAME)

                      LEFT JOIN AF_DEP_USER ADU

                                           on( WK.USER_ID=ADU.USER_NAME)

                     LEFT JOIN AF_DEP_INFO ADI

                                           on(ADU.DEP_ID=ADI.DEP_ID)

                     LEFT JOIN WF_REG_USERS O_WRU

                                           on(WK.USER_ID=O_WRU.USER_NAME)

                     LEFT JOIN AF_USER_INFO O_AUI

                                          on(WK.USER_ID=O_AUI.USER_NAME)

                     LEFT JOIN AF_DEP_USER O_ADU

                                          on( WK.USER_ID=O_ADU.USER_NAME)

                    LEFT JOIN AF_DEP_INFO O_ADI

                                          on(O_ADU.DEP_ID=O_ADI.DEP_ID)

where PIS.PROC_INST_NAME='通用表單-2009/12/1 10:51:30(791)'

and PIS.PROC_INST_ID=WK.PROC_INST_ID

and WK.ACTIVITY_INST_ID=WAI.ID

order by WK.ASSIGNED_DATE,WAI.NAME,WK.SESSION_