流程审批信息查询SQL
最初的依赖
1,205次浏览
编辑于2019年05月07日 17:48:05
仅供参考，不喜勿喷
select * FROM(
SELECT
T0.FID "多级审批对象id",
T0.FCreateTime "审批时间",
T4.FName_l2 "审批人名称",
T0.FOpinion_l2 "审批意见",
T0.FHandlerContent "决策内容",
T0.FBillID "审批单据ID",
T0.FIsPass "审批结果",
T2.FACTDEFNAME_l2 "任务名称",
T0.FBosType "单据类型",
T2.FACTINSTID,
T2.FCREATEDTIME
FROM T_BAS_MultiApprove T0
INNER JOIN T_PM_User T1
ON T0.FCreatorID = T1.FID
LEFT OUTER JOIN T_WFR_AssignDetail T2
ON T0.FASSIGNMENTID = T2.FASSIGNID
INNER JOIN T_BD_Person T4
ON T1.FPersonId = T4.FID
where 1=1
and T0.Fbillid ='单据ID'
union all
select
null "多级审批对象id",
max(FCREATEDTIME) "审批时间",
FPRIORPERFORMERNAME_L2 "审批人名称",
null "审批意见",
null "决策内容",
fbizobjid "审批单据ID",
'true' "审批结果",
FACTDEFNAME_l2 "任务名称",
null "单据类型",
FACTINSTID,
max(FCREATEDTIME) FCREATEDTIME
from T_WFR_AssignDetail
where fbizobjid in (select fid from CT_SM_PayLoaclGood where fnumber = 'SZPA20190425008')
and FACTINSTID not in (
SELECT T2.FACTINSTID
FROM T_BAS_MultiApprove T0
LEFT OUTER JOIN T_WFR_AssignDetail T2
ON T0.FASSIGNMENTID = T2.FASSIGNID
where 1=1
and T0.Fbillid = '单据ID'
)
group by FPRIORPERFORMERNAME_L2, fbizobjid, FACTDEFNAME_l2, FACTINSTID
) T0 ORDER BY T0.FCREATEDTIME
select * FROM(
SELECT
T0.FID "多级审批对象id",
T0.FCreateTime "审批时间",
T4.FName_l2 "审批人名称",
T0.FOpinion_l2 "审批意见",
T0.FHandlerContent "决策内容",
T0.FBillID "审批单据ID",
T0.FIsPass "审批结果",
T2.FACTDEFNAME_l2 "任务名称",
T0.FBosType "单据类型",
T2.FACTINSTID,
T2.FCREATEDTIME
FROM T_BAS_MultiApprove T0
INNER JOIN T_PM_User T1
ON T0.FCreatorID = T1.FID
LEFT OUTER JOIN T_WFR_AssignDetail T2
ON T0.FASSIGNMENTID = T2.FASSIGNID
INNER JOIN T_BD_Person T4
ON T1.FPersonId = T4.FID
where 1=1
and T0.Fbillid ='单据ID'
union all
select
null "多级审批对象id",
max(FCREATEDTIME) "审批时间",
FPRIORPERFORMERNAME_L2 "审批人名称",
null "审批意见",
null "决策内容",
fbizobjid "审批单据ID",
'true' "审批结果",
FACTDEFNAME_l2 "任务名称",
null "单据类型",
FACTINSTID,
max(FCREATEDTIME) FCREATEDTIME
from T_WFR_AssignDetail
where fbizobjid in (select fid from CT_SM_PayLoaclGood where fnumber = 'SZPA20190425008')
and FACTINSTID not in (
SELECT T2.FACTINSTID
FROM T_BAS_MultiApprove T0
LEFT OUTER JOIN T_WFR_AssignDetail T2
ON T0.FASSIGNMENTID = T2.FASSIGNID
where 1=1
and T0.Fbillid = '单据ID'
)
group by FPRIORPERFORMERNAME_L2, fbizobjid, FACTDEFNAME_l2, FACTINSTID
) T0 ORDER BY T0.FCREATEDTIME
推荐阅读