EAS 审批流相关表

我们知道在EAS早期版本，是没有连接属性的说法，审批流相关表的关联关系都是自有属性。

今天给大家分享几段关于EAS审批流相关表的关联关系，希望大家可用到。

这里以费用报销单的审批流为例：

SELECT detail.FAssignID assignid,     	--审批结果bill.FNUMBER billnumber,		--单据编号act.FActDefName_l2 actName,      	--活动定义名称detail.FPersonuserName_l2,		--用户名称bill.FCause cause,			--事由approve.FOpinion_L2 opinion,          --审批意见--approve.FHandlerContent context, 	--决策选项值approve.FIsPass isPass, 		--审批结果approve.FcreateTime createTime,	--创建时间posit.FName_l2 position,		--人员adm.FName_l2 adminorg		--组织
FROM t_wfr_actInst ACT   --活动实例											
INNER JOIN t_WFR_ProcinstRef pr ON act.FPROCINSTID = pr.FPROCINSTID   		--流程实例引用关系
INNER JOIN T_WFR_ProcInst pro ON pro.FPROCINSTID = act.FPROCINSTID 		--流程实例
INNER JOIN t_wfr_Assigndetail detail ON detail.FACTDEFID = act.FACTDEFID 	
AND detail.FPROCINSTID = act.FPROCINSTID					--处理过的工作流任务
INNER JOIN T_BC_BizAccountBill bill ON BILL.FID = pr.frefId			--需要查询审批流的表
INNER JOIN T_BAS_MultiApprove approve ON approve.FASSIGNMENTID=detail.FAssignID	--多级审批
INNER JOIN T_BD_Person person ON person.fid=detail.FPersonEMPID		--人员
INNER JOIN T_ORG_PositionMember pmb ON person.FID = pmb.FPersonID	
AND pmb.FisPrimary=1							--职员任职情况
INNER JOIN T_ORG_Position posit ON pmb.FPositionID = posit.FID			--职位
INNER JOIN T_ORG_Admin adm ON posit.FAdminOrgUnitID = adm.FID			--组织
WHERE BILL.FNUMBER='ETE-SSCGZ-20180625-002'

2.查询审批中报销单的每级审批人清单

select v.fnumber 单据编码,
v.CFK3billnumber K3单据编码,
f.FName_L2 费用支付公司,
v.CFTotalamt 单据金额,
c.fname_l2 报销人,
v.FBizDate 业务日期,
--h.FACTDEFNAME_L2 已审批节点,
--h.FPERSONUSERNAME_L2 已审批人,
i.FACTDEFNAME_L2 未审批节点,
i.FPERSONUSERNAME_L2 未审批人,
v.CFBillState 单据状态,
e.fdisplayname_l2 公司,
e.FName_L2 费用支付部门
 from CT_PAY_TAndEBill v
left join T_BD_Person c on c.fid = v.CFAppEmployeeID
left join T_ORG_BaseUnit e on e.fid=v.CFPaydeptID
left join T_ORG_BaseUnit f on f.fid=v.CFPaycompanyID
--left join T_WFR_AssignDetail h on v.fid=h.FBIZOBJID
left join T_WFR_Assign i on v.fid=i.FBIZOBJID
where i.FACTDEFNAME_L2 IN ('一级审批','二级审批','三级审批','四级审批','SSC财务复核','财务复核','Finance FD') 
--or h.FACTDEFNAME_L2 IN ('一级审批','二级审批','三级审批','四级审批','SSC财务复核','财务复核','Finance FD') 
and v.FCreateTime >='2018-06-01 00:00:00'
--and h.FSTATE=16
and i.FSTATE=1
and i.FACTDEFNAME_L2<>'废弃'
and v.CFBillState in (3,5,7,10)
and v.fnumber='ETE-SCGZ-20181213-009'

3：查询付款单指定时间段第三集审批人为某某时

SELECT tab2.FName_L2 AS "组织名称",
       tab1.FNumber  AS "单据编码",
       tab1.FBillDate,
       tab3.FACTDEFNAME_L2  --,tab4.FACTDEFNAME_L2
FROM T_CAS_PaymentBill tab1
LEFT JOIN t_org_baseunit tab2 ON tab1.fcompanyid = tab2.FID
LEFT JOIN T_WFR_AssignDetail tab3 ON tab3.FBIZOBJID = tab1.FID    
--LEFT JOIN T_WFR_Assign tab4 ON tab3.FBIZOBJID = tab1.FID    
WHERE --tab1.FNumber='FK201STSH19050001'
       tab1.FActualPayAmount > 3000000
       AND tab1.FCreateTime >= '20190401'
       AND tab1.FCreateTime <= '20190430' 
       AND tab3.FACTDEFNAME_L2 = '三级审批'
       AND tab3.FSTATE = '16'
       AND tab3.FPERSONUSERNAME_L2='Pauline Mo_莫亦平'
ORDER BY tab2.FName_L2

3：查看某单据审批流当前处理人

select
Case b.FID when null then '空值' else '费用报销单' end as BillType,
b.fid as fid,
c.fnumber AS "组织编码",
c.FName_L2 AS "组织名称",
b.FNumber AS "单据编码",
case when d.FSTATE = 'open.not_running.not_started' then '未开始'
     when d.FSTATE = 'open.not_running.suspended' then '已挂起'
     when d.FSTATE = 'open.running' then '运行中'
     when d.FSTATE = 'closed.completed' then '已完成'
     when d.FSTATE = 'closed.aborted' then '已撤消'
     when d.FSTATE = 'closed.terminated' then '已终止' end as Fstate,
Assign.FACTDEFNAME_L2 AS "当前处理节点",
Assign.FPERSONEMPNAME_L2 AS "当前处理人姓名",
Assign.FLASTSTATETIME AS "到达当前处理人时间",
Assign.FSUBJECT_L2 AS "待办消息内容",
Assign.FPROCDEFNAME_L2 as "流程定义名称"
from T_WFR_Assign Assign
 left join T_BC_BizAccountBill b on Assign.FBIZOBJID = b.fid
 left join t_org_baseunit c on b.fcompanyid = c.fid
 left join T_WFR_ProcInst d on Assign.FPROCINSTID = d.FPROCINSTID
 where b.fnumber is not null
   and c.fnumber like '@CompanyNO%'
   and upper(b.FNumber) like upper ('%@BillNO%')
   and upper(Assign.FPERSONEMPNAME_L2) like upper ('%@Person%')
   and upper(Assign.FACTDEFNAME_L2) like upper('%@ActdefName%')
   and upper(Assign.FPROCDEFNAME_L2) like upper('%@ProcdefName%')
order by
c.fnumber,b.FNumber,
Assign.FACTDEFNAME_L2,
Assign.FPERSONEMPNAME_L2