、、、 DECLARE @FormId VARCHAR(50) DECLARE @FBillNo NVARCHAR(50) SET @FormId='PUR_PurchaseOrder' SET @FBillNo='CGDD001976' ---- 查询元数据信息 --SELECT a.FID AS 业务对象标识,b.FNAME AS 业务对象名称,CAST(FKERNELXML.query('//HeadEntity/TableName/text()') AS VARCHAR) AS 主表名,a.* --FROM T_META_OBJECTTYPE a --LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 --WHERE a.FMODELTYPEID<=400 AND b.FNAME LIKE '%采购订单%' ---- AND (a.FID LIKE '%PUR_PurchaseOrder%' OR FINHERITPATH LIKE '%PUR_PurchaseOrder%') -- 查询单据信息 DECLARE @FID INT SELECT @FID=FID FROM T_PUR_POORDER WHERE FBILLNO=@FBillNo SELECT '单据信息' ,a.FBILLNO AS 单据编号 ,(CASE a.FDOCUMENTSTATUS WHEN 'A' THEN '创建' WHEN 'B' THEN '审核中' WHEN 'C' THEN '已审核' WHEN 'D' THEN '重新审核' WHEN 'Z' THEN '暂存' ELSE '' END) AS 单据状态 ,b.FNAME AS 最后审批人,FAPPROVEDATE AS 最后审批时间,c.FNAME AS 最后修改人,a.FMODIFYDATE AS 最后修改时间,a.* FROM T_PUR_POORDER a LEFT JOIN T_SEC_USER b ON a.FAPPROVERID=b.FUSERID LEFT JOIN T_SEC_USER c ON a.FMODIFIERID=c.FUSERID WHERE a.FBILLNO=@FBillNo -- 查询流程实例信息 DECLARE @FPROCINSTID VARCHAR(50) SELECT @FPROCINSTID=FPROCINSTID FROM T_WF_PROCINST WHERE FNUMBER LIKE @FBillNo+'%' --AND FSTATUS='4' SELECT '流程实例信息(T_WF_PROCINST)' ,(CASE a.FSTATUS WHEN '1' THEN '完成' WHEN '2' THEN '运行中' WHEN '3' THEN '挂起' WHEN '4' THEN '终止' WHEN '5' THEN '暂停' ELSE '' END) AS 流程状态 ,a.FNUMBER AS 流程实例编码,a.FPROCINSTID AS 流程实例内码,b.FNUMBER AS 流程编码,c.FDISPLAYNAME AS 流程名称 ,d.FVERSION AS 流程版本,d.FDEFINITIONXML AS 流程定义XML,d.FDESIGNERXML AS 流程设计XML ,a.FFAILUREOUTINFO AS 错误信息,a.FFAILUREMESSAGE AS 错误堆栈 ,a.* FROM T_WF_PROCINST a LEFT JOIN T_WF_PROCDEF b ON a.FPROCDEFID=b.FPROCDEFID LEFT JOIN T_WF_PROCDEF_L c ON b.FPROCDEFID=c.FPROCDEFID AND c.FLOCALEID=2052 LEFT JOIN T_WF_DEFVERSION d ON a.FVERSIONID=d.FVERSIONID WHERE a.FNUMBER LIKE @FBillNo+'%' -- 查询流程节点信息 SELECT '流程节点信息(T_WF_ACTINST)' ,b.FACTNAME AS 节点名称 ,CASE a.FSTATUS WHEN '0' THEN '准备(未处理)' WHEN '1' THEN '完成(已处理)' WHEN '2' THEN '空闲/挂起(运行中)' WHEN '3' THEN '被舍弃' WHEN '4' THEN '失败' WHEN '5' THEN '终止' WHEN '6' THEN '驳回' ELSE '' END AS 节点状态 ,a.FACTINSTID AS 节点内码,a.* FROM T_WF_ACTINST a LEFT JOIN T_WF_ACTINST_L b ON a.FACTINSTID=b.FACTINSTID AND b.FLOCALEID=2052 WHERE a.FPROCINSTID=@FPROCINSTID -- 查询流程节点实例消息 SELECT '流程节点实例消息(T_WF_ACTINSTMSG)' ,d.FACTNAME AS 节点名称 ,e.FNAME AS 接收人 ,a.FCREATETIME AS 接收时间 ,a.*,c.FACTINSTID FROM T_WF_ACTINSTMSG a LEFT JOIN T_WF_PROCINST b ON a.FPROCINSTID=b.FPROCINSTID LEFT JOIN T_WF_ACTINST c ON b.FPROCINSTID=c.FPROCINSTID AND a.FACTIVITYID=c.FACTIVITYID LEFT JOIN T_WF_ACTINST_L d ON c.FACTINSTID=d.FACTINSTID AND d.FLOCALEID=2052 LEFT JOIN T_SEC_USER e ON a.FRECEIVERID=e.FUSERID WHERE a.FPROCINSTID=@FPROCINSTID -- 查询节点动作执行日志信息 SELECT '节点动作执行日志信息(T_WF_ACTIONEVENTLOG)' ,c.FACTNAME AS 节点名称 ,a.FEXECUTEDTIME AS 执行时间 ,a.FEXECUTELOG AS 执行日志 ,a.* FROM T_WF_ACTIONEVENTLOG a LEFT JOIN T_WF_ACTINST b ON a.FACTINSTID=b.FACTINSTID LEFT JOIN T_WF_ACTINST_L c ON b.FACTINSTID=c.FACTINSTID AND c.FLOCALEID=2052 WHERE b.FPROCINSTID=@FPROCINSTID -- 查询待办任务信息 SELECT '待办任务信息(T_WF_ASSIGN)' ,c.FACTNAME AS 节点名称 ,CASE a.FSTATUS WHEN '0' THEN '未处理' WHEN '1' THEN '已处理' ELSE '' END AS 待办任务状态 ,b.FNAME AS 发送人,a.FASSIGNID AS 待办任务内码,a.* FROM T_WF_ASSIGN a LEFT JOIN T_SEC_USER b ON a.FSENDERID=b.FUSERID LEFT JOIN T_WF_ACTINST_L c ON a.FACTINSTID=c.FACTINSTID AND c.FLOCALEID=2052 WHERE a.FPROCINSTID=@FPROCINSTID -- 查询待办任务接收人信息 SELECT '待办任务接收人信息(T_WF_RECEIVER)' ,c.FACTNAME AS 节点名称 ,d.FNAME AS 接收人 ,a.* FROM T_WF_RECEIVER a LEFT JOIN T_WF_ASSIGN b ON a.FASSIGNID=b.FASSIGNID LEFT JOIN T_WF_ACTINST_L c ON b.FACTINSTID=c.FACTINSTID AND c.FLOCALEID=2052 LEFT JOIN T_SEC_USER d ON a.FRECEIVERID=d.FUSERID WHERE b.FPROCINSTID=@FPROCINSTID -- 查询审批任务信息 SELECT '审批任务信息(T_WF_APPROVALASSIGN)' ,c.FACTNAME AS 节点名称 ,a.* FROM T_WF_APPROVALASSIGN a LEFT JOIN T_WF_ASSIGN b ON a.FASSIGNID=b.FASSIGNID LEFT JOIN T_WF_ACTINST_L c ON b.FACTINSTID=c.FACTINSTID AND c.FLOCALEID=2052 WHERE b.FPROCINSTID=@FPROCINSTID -- 查询审批任务处理明细信息 SELECT '审批任务处理明细信息(T_WF_APPROVALITEM)' AS 审批任务处理明细信息 ,e.FACTNAME AS 节点名称 ,d.FNAME AS 接收人 ,CASE a.FSTATUS WHEN '0' THEN '未处理' WHEN '1' THEN '已处理' WHEN '3' THEN '转发' WHEN '4' THEN '准备中(顺序会签)' ELSE '' END AS 处理状态 ,a.* FROM T_WF_APPROVALITEM a LEFT JOIN T_WF_APPROVALASSIGN b ON a.FAPPROVALASSIGNID=b.FAPPROVALASSIGNID LEFT JOIN T_WF_ASSIGN c ON b.FASSIGNID=c.FASSIGNID LEFT JOIN T_SEC_USER d ON a.FRECEIVERID=d.FUSERID LEFT JOIN T_WF_ACTINST_L e ON c.FACTINSTID=e.FACTINSTID AND e.FLOCALEID=2052 WHERE c.FPROCINSTID=@FPROCINSTID -- 查询工作流日志信息 SELECT '工作流日志信息(T_WF_LOG)' ,c.FACTNAME AS 节点名称 ,b.FNAME AS 操作人 ,a.* FROM T_WF_LOG a LEFT JOIN T_SEC_USER b ON a.FCREATORID=b.FUSERID LEFT JOIN T_WF_ACTINST_L c ON a.FACTINSTID=c.FACTINSTID AND c.FLOCALEID=2052 WHERE a.FPROCINSTID=@FPROCINSTID -- 查询上机操作日志 SELECT '上机操作日志(单据编号)' AS [上机操作日志(单据编号)] ,a.FCLIENTTYPE AS 客户端类型,b.FNAME AS 用户名,a.FDATETIME AS 操作时间,a.FDESCRIPTION AS 操作描述,a.* FROM T_BAS_OPERATELOG a LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID WHERE a.FOBJECTTYPEID=@FormId AND a.FDESCRIPTION LIKE '%'+@FBillNo+'%' ORDER BY a.FDATETIME -- 查询已归档上机操作日志 SELECT '已归档上机操作日志(单据编号)' AS [已归档上机操作日志(单据编号)] ,a.FCLIENTTYPE AS 客户端类型,b.FNAME AS 用户名,a.FDATETIME AS 操作时间,a.FDESCRIPTION AS 操作描述,a.* FROM T_BAS_OPERATELOGBK a LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID WHERE a.FOBJECTTYPEID=@FormId AND a.FDESCRIPTION LIKE '%'+@FBillNo+'%' ORDER BY a.FDATETIME ---- 查询上机操作日志 --SELECT '上机操作日志(单据内码)' AS [上机操作日志(单据内码)] --,a.FCLIENTTYPE AS 客户端类型,b.FNAME AS 用户名,a.FDATETIME AS 操作时间,a.FDESCRIPTION AS 操作描述,a.* --FROM T_BAS_OPERATELOG a --LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID --WHERE a.FOBJECTTYPEID=@FormId AND a.FINTERID=CAST(@FID AS VARCHAR(50)) --ORDER BY FDATETIME ---- 查询已归档上机操作日志 --SELECT '已归档上机操作日志(单据内码)' AS [已归档上机操作日志(单据内码)] --,a.FCLIENTTYPE AS 客户端类型,b.FNAME AS 用户名,a.FDATETIME AS 操作时间,a.FDESCRIPTION AS 操作描述,a.* --FROM T_BAS_OPERATELOGBK a --LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID --WHERE a.FOBJECTTYPEID=@FormId AND a.FINTERID=CAST(@FID AS VARCHAR(50)) --ORDER BY FDATETIME -- 查询元数据信息 SELECT '元数据信息' AS 元数据信息 ,a.FID AS 业务对象标识,b.FNAME AS 业务对象名称,CAST(FKERNELXML.query('//HeadEntity/TableName/text()') AS VARCHAR) AS 主表名,a.* FROM T_META_OBJECTTYPE a LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 WHERE a.FMODELTYPEID<=400 AND (a.FID LIKE '%'+@FormId+'%' OR FINHERITPATH LIKE '%'+@FormId+'%') ---- 查询单据已审核流程未结束的异常单据 --SELECT '单据已审核流程未结束的异常单据' AS 单据已审核流程未结束的异常单据 --,c.FNUMBER AS 流程实例编码,c.FPROCINSTID AS 流程实例内码,a.FBILLNO AS 单号,a.* --FROM T_PUR_POORDER a --JOIN T_WF_PIBIMAP b ON b.FOBJECTTYPEID=@FormId AND a.FID=b.FKEYVALUE --JOIN T_WF_PROCINST c ON b.FPROCINSTID=c.FPROCINSTID --WHERE a.FDOCUMENTSTATUS = 'C' AND c.FSTATUS='2' ---- 流程已结束单据未审核的异常单据 --SELECT '流程已结束单据未审核的异常单据' AS 流程已结束单据未审核的异常单据 --,c.FNUMBER AS 流程实例编码,c.FPROCINSTID AS 流程实例内码,a.FBILLNO AS 单号,a.* --FROM T_PUR_POORDER a --JOIN T_WF_PIBIMAP b ON b.FOBJECTTYPEID=@FormId AND a.FID=b.FKEYVALUE --JOIN T_WF_PROCINST c ON b.FPROCINSTID=c.FPROCINSTID --WHERE a.FDOCUMENTSTATUS <> 'C' AND c.FSTATUS<>'2' 作者：Jack 来源：金蝶云社区 原文链接：https://wenku.my7c.com/article/376296304433066752?productLineId=1&isKnowledge=2 著作权归作者所有。未经允许禁止转载，如需转载请联系作者获得授权。