Defect: Attestation无法选择alternative approver

项目场景：

正常情况下，基本上每一个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;