簽核流程設置

 /*
---游標處理批量流程---------
---步驟1,建立表--檢查ProjectCode是否帶有特殊字符
---步驟2---檢查所有簽核人帳號是否存在---
--檢查導入簽核人是否正確（主要判斷數據是否為NULL）
select distinct [級數] from dbo.[zlq_PCO1107]
select *  from dbo.[zlq_PCO1107] where [級數]=1 and ([一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=2 and ([二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=3 and ([三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=4 and ([四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=5 and ([五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=6 and ([六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=7 and ([七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=8 and ([八級簽核] is NULL or [七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
---檢查是否存在于表dbo.PCO_CheckMan中-----     

----首先帳號是否存在于表dbo.PCO_CheckMan中---
----其次真實帳號是否存在于表dbo.Ac_BRM_User中---
select a.[一級簽核],b.Username,c.CheckManID from (select distinct [一級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[一級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[二級簽核],b.Username,c.CheckManID from (select distinct [二級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[二級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[三級簽核],b.Username,c.CheckManID from (select distinct [三級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[三級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[四級簽核],b.Username,c.CheckManID from (select distinct [四級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[四級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[五級簽核],b.Username,c.CheckManID from (select distinct [五級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[五級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[六級簽核],b.Username,c.CheckManID from (select distinct [六級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[六級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username