项目场景:
正常情况下,基本上每一个attestation policy都有两种approver,一个是正常的approver,如果根据数据找不到approver,会有一个alternative approver来进行审批。
问题描述:
问题就出在当前没有approver,但是也不分配给alternative approver进行审批。
下图就是这个流程,实际上,左边蓝色方框里是没有找到approver的,但是在红色菱形判断的结果却是走到了条件满足的蓝色区域。按照原始需求,应该是走到右边紫色的方框步骤中。
原因分析:
Attestation是可以配置chief approval team成员的。这个team成员可以审批任何case。在实际中,这个team的成员并不会干涉正常的审核,只有在极为特殊的情况下才越俎代庖。
在之前的使用中,是没有对其进行配置。当这个team配置了成员以后,菱形的判断SQL将其判断成正常的审核人员,认为蓝色区域是有审核人的。
下面是菱形内配置的判断SQL。
EXISTS (
SELECT 1
FROM AttestationCase ac
JOIN AttestationHelper ah ON ac.UID_AttestationCase = ah.UID_AttestationCase
JOIN (
SELECT ac.UID_AttestationCase
,s.UID_QERWorkingMethod
,ah.LevelNumber
,ac.DecisionLevel + s.PositiveSteps AS 'TargetLevel'
FROM AttestationCase ac
JOIN AttestationHelper ah ON ac.UID_AttestationCase = ah.UID_AttestationCase
AND ac.DecisionLevel = ah.LevelNumber
JOIN QERWorkingStep s ON ah.UID_QERWorkingStep = s.UID_QERWorkingStep
WHERE ac.UID_AttestationCase = '@UID_AttestationCase'
) AS x ON ac.UID_AttestationCase = x.UID_AttestationCase
AND ah.LevelNumber = x.LevelNumber
JOIN AttestationHelper ah2 ON ac.UID_AttestationCase = ah2.UID_AttestationCase
AND ah2.LevelNumber = x.TargetLevel
JOIN QERWorkingStep s2 ON x.UID_QERWorkingMethod = s2.UID_QERWorkingMethod
AND s2.LevelNumber = x.TargetLevel
GROUP BY ah.UID_AttestationCase
,s2.CountApprover
HAVING COUNT(*) >= s2.CountApprover
)
解决方案:
解决方案就是修改菱形中的SQL,将chief approval team里的成员都过滤掉。
在倒数第四行添加了一行内容 where ah2.rulerlevel = 0
EXISTS (
SELECT 1
FROM AttestationCase ac
JOIN AttestationHelper ah ON ac.UID_AttestationCase = ah.UID_AttestationCase
JOIN (
SELECT ac.UID_AttestationCase
,s.UID_QERWorkingMethod
,ah.LevelNumber
--当前的节点level + 正向节点数 = 下一个节点的level
,ac.DecisionLevel + s.PositiveSteps AS 'TargetLevel'
FROM AttestationCase ac
JOIN AttestationHelper ah ON ac.UID_AttestationCase = ah.UID_AttestationCase
AND ac.DecisionLevel = ah.LevelNumber
JOIN QERWorkingStep s ON ah.UID_QERWorkingStep = s.UID_QERWorkingStep
WHERE ac.UID_AttestationCase = '@UID_AttestationCase'
) AS x ON ac.UID_AttestationCase = x.UID_AttestationCase
AND ah.LevelNumber = x.LevelNumber
JOIN AttestationHelper ah2 ON ac.UID_AttestationCase = ah2.UID_AttestationCase
AND ah2.LevelNumber = x.TargetLevel
JOIN QERWorkingStep s2 ON x.UID_QERWorkingMethod = s2.UID_QERWorkingMethod
AND s2.LevelNumber = x.TargetLevel
where ah2.rulerlevel = 0
GROUP BY ah.UID_AttestationCase
,s2.CountApprover
HAVING COUNT(*) >= s2.CountApprover
)
知识点回顾
- AttestationCase 表记录了验证case的信息。
- AttestationHelper 表记录了系统算出来的全部可能的审核人员。每个一条记录。
- 以下两句SQL用来找出chief approval team的成员有哪些。顺便提一下,oneIM里有两个chief approval team,一个是在attestation里,一个在IT shop里。
select a.Ident_AERole, a.FullPath, a.* from AERole a where a.Ident_AERole='Chief approval team';
select * from PersonInAERole a where a.UID_AERole='ATT-AEROLE-ATTESTATION-INTERVENTION';
- 相关表
select * from PWODecisionMethod; --approval policy
select * from PWODecisionSubMethod a where a.UsageArea='A' order by 2; --approval workflow
select * from PWODecisionStep; --approval workflow step
select * from PWODecisionRule; --approval procedure
select * from PWODecisionRuleRulerDetect; --definition of approval procedure
--下面两张表,记录着类似的信息,并且有外键关联,但是区别在哪里,还没搞清楚
select * from QERWorkingMethod a order by a.Ident_PWODecisionSubMethod;
select * from PWODecisionSubMethod a order by a.Ident_PWODecisionSubMethod;
--下面两张表,记录着类似的信息,并且有外键关联,但是区别在哪里,还没搞清楚
select * from QERWorkingStep a order by a.Ident_PWODecisionStep;
select * from PWODecisionStep a order by a.Ident_PWODecisionStep;