与流程JBPM相关

http://tomkoo.iteye.com/blog/35747  顾总关于JBPM

--查询流程实例下的执行的任务
select pii.ID_,pinfo.INFO,ti.* from jbpm_moduleinstance m,jbpm_processinstance pii,
jbpm_taskinstance ti, CMCS3_PROCESS_INSTANCE_INFO pinfo
where m.PROCESSINSTANCE_ = pii.ID_ and m.ID_=ti.TASKMGMTINSTANCE_
and pinfo.PROCESSINSTANCEID = pii.ID_
and pii.ID_= 999999999999;

--查询任务实例的用户(根据jbpm_taskinstance 的ID 找到对应流程节点的用户)
select ti.ID_,pa.ACTORID_ from jbpm_pooledactor pa,jbpm_taskinstance ti,JBPM_TASKACTORPOOL tap
where pa.ID_=tap.POOLEDACTOR_ and ti.ID_=tap.TASKINSTANCE_
and ti.ID_ = 532;(id 是jbpm_taskinstance 的ID)

--查询用户下的单据
select distinct taskinstan2_.CREATE_ as col_0_0_, processins3_.Info as col_1_0_, taskinstan2_.NAME_ as col_2_0_, taskinstan2_.ID_ as col_3_0_
from JBPM_POOLEDACTOR pooledacto0_, JBPM_TASKACTORPOOL taskinstan1_, JBPM_TASKINSTANCE taskinstan2_, CMCS3_PROCESS_INSTANCE_INFO processins3_,
JBPM_MODULEINSTANCE taskmgmtin4_
where taskinstan2_.TASKMGMTINSTANCE_=taskmgmtin4_.ID_
and pooledacto0_.ID_=taskinstan1_.POOLEDACTOR_
and taskinstan1_.TASKINSTANCE_=taskinstan2_.ID_ and pooledacto0_.ACTORID_='[e-mail]'
and taskmgmtin4_.PROCESSINSTANCE_=processins3_.ProcessInstanceId
and  (taskinstan2_.ACTORID_ is null)
and taskinstan2_.ISOPEN_=1 order by taskinstan2_.ID_ desc;

--根据单据信息查出流程节点
select ti.ID_,pa.ACTORID_ from jbpm_pooledactor pa,jbpm_taskinstance ti,JBPM_TASKACTORPOOL tap
where pa.ID_=tap.POOLEDACTOR_ and ti.ID_=tap.TASKINSTANCE_
and ti.ID_ in(select ti.id_ from jbpm_moduleinstance m,jbpm_processinstance pii,
jbpm_taskinstance ti, CMCS3_PROCESS_INSTANCE_INFO pinfo
where m.PROCESSINSTANCE_ = pii.ID_ and m.ID_=ti.TASKMGMTINSTANCE_
and pinfo.PROCESSINSTANCEID = pii.ID_
and pii.ID_ in(select b.processinstanceid from cmcs3_transfer_bill b where b.voucherno = '凭证号'));

--所有流程组中的制单，复核，审批人的姓名，和节点位置
select distinct pt.taskname,ptu.posts,ptu.actors from cmcs3_process_task pt,cmcs3_process_task_user ptu,cmcs3_process p
where pt.processid = p.processid and pt.processtaskid = ptu.processtaskid
and p.uniquecode = 'ExternalPayments';