107 lines
3.7 KiB
Plaintext
107 lines
3.7 KiB
Plaintext
CREATE PROC DBO.GEXBPM_GENFLOW4
|
|
(
|
|
@G_BILLNO VARCHAR(20) --簽核單號
|
|
)
|
|
/*
|
|
Function : 重新產生 BPM_FLOWDATA_D
|
|
Description: 本作業是當原單已產生 FLOWDATA
|
|
但又因群組人員有變動, 要批次更新變更後的群組人員
|
|
|
|
目前應該是在 BPMMG1 的 [重置人員] 有呼叫
|
|
如果傳入單號, 表示單張, 否則就是全重置
|
|
|
|
Build Date : 2013/08/02
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
|
|
*/
|
|
AS
|
|
|
|
--一般群組的重置
|
|
UPDATE BPM_FLOWDATA_D SET SIGNPERSON = ISNULL((SELECT TOP 1 USERID FROM USERGROUPS WHERE USERGROUPS.GROUPID = BPM_FLOWDATA_D.ORG_GROUP),SIGNPERSON)
|
|
WHERE ISNULL(ORG_GROUP,'') NOT IN ('MANAGER','AGENT','FIELDDEF','') AND (FLOWSTATUS = 0 OR FLOWSTATUS = 2)
|
|
|
|
/*
|
|
直屬主管及代理人 的批次變更
|
|
*/
|
|
DECLARE @BILLNO VARCHAR(100)
|
|
DECLARE @PRE_BILLNO VARCHAR(100)
|
|
DECLARE @SIGNPERSON VARCHAR(100)
|
|
DECLARE @PRE_PERSON VARCHAR(100)
|
|
DECLARE @ORG_PERSON VARCHAR(100)
|
|
DECLARE @ORG_GROUP VARCHAR(100)
|
|
DECLARE @BOSS_PERSON VARCHAR(100)
|
|
DECLARE @SEQ INT
|
|
DECLARE @LAVEL VARCHAR(10)
|
|
DECLARE @DEPARTMENTID VARCHAR(20) --原單部門
|
|
|
|
DECLARE contact_cursor3 CURSOR FOR
|
|
SELECT B.BILLNO,A.SEQNO,A.SIGNPERSON,A.ORG_GROUP,B.SOURCEDEPARTMENT FROM BPM_FLOWDATA_D A
|
|
LEFT JOIN BPM_FLOWDATA_M B ON A.BILLNO = B.BILLNO
|
|
WHERE (CASE WHEN ISNULL(@G_BILLNO,'')='' THEN @G_BILLNO ELSE B.BILLNO END) = @G_BILLNO AND B.FLOWSTATUS < 4 --未完
|
|
ORDER BY B.BILLNO,A.SEQNO
|
|
|
|
OPEN contact_cursor3
|
|
|
|
-- Perform the first fetch.
|
|
FETCH NEXT FROM contact_cursor3
|
|
INTO @BILLNO,@SEQ,@SIGNPERSON,@ORG_GROUP,@DEPARTMENTID
|
|
|
|
--ORG_PERSON 原上呈人員, 不變(代理人永遠是真對原上呈人)
|
|
--PRE_PERSON 前一關的簽核人, 是針對 MANAGER 用, 每一關都會變
|
|
SET @ORG_PERSON = @SIGNPERSON
|
|
SET @PRE_PERSON = @SIGNPERSON
|
|
SET @PRE_BILLNO = @BILLNO
|
|
|
|
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF @PRE_BILLNO <> @BILLNO SET @ORG_PERSON = @SIGNPERSON
|
|
|
|
SET @BOSS_PERSON = @SIGNPERSON
|
|
IF @ORG_GROUP = 'MANAGER'
|
|
BEGIN
|
|
SELECT @BOSS_PERSON=EMP_NO FROM BASPERSON WHERE PERSONID = @PRE_PERSON
|
|
|
|
UPDATE BPM_FLOWDATA_D SET SIGNPERSON = @BOSS_PERSON
|
|
WHERE BILLNO = @G_BILLNO AND SEQNO = @SEQ AND ORG_GROUP = 'MANAGER' AND (FLOWSTATUS = 0 OR FLOWSTATUS = 2)
|
|
END
|
|
ELSE IF @ORG_GROUP = 'AGENT'
|
|
BEGIN
|
|
SELECT @BOSS_PERSON=ISNULL(AGENTID,'') FROM BASPERSON_AGENT WHERE PERSONID = @ORG_PERSON AND PRIOITY = (SELECT MIN(PRIOITY) FROM BASPERSON_AGENT WHERE PERSONID = @ORG_PERSON)
|
|
|
|
UPDATE BPM_FLOWDATA_D SET SIGNPERSON = @BOSS_PERSON
|
|
WHERE BILLNO = @G_BILLNO AND SEQNO = @SEQ AND ORG_GROUP = 'AGENT' AND (FLOWSTATUS = 0 OR FLOWSTATUS = 2)
|
|
END
|
|
--以下為專案特殊寫法
|
|
ELSE IF SUBSTRING(@ORG_GROUP,1,7) = 'DEPBOSS'
|
|
BEGIN
|
|
--取得部門檔的簽核主管(PICK 專案, 但也可以變成標準版本, 目前最多6階)
|
|
SET @LAVEL = SUBSTRING(@ORG_GROUP,8,1)
|
|
SELECT
|
|
@BOSS_PERSON=
|
|
CASE WHEN @LAVEL = '1' THEN DE1_LEADER
|
|
WHEN @LAVEL = '2' THEN DE2_LEADER
|
|
WHEN @LAVEL = '3' THEN DE3_LEADER
|
|
WHEN @LAVEL = '4' THEN DE4_LEADER
|
|
WHEN @LAVEL = '5' THEN DE5_LEADER
|
|
WHEN @LAVEL = '6' THEN DE6_LEADER
|
|
ELSE ISNULL(DE1_LEADER,'') END
|
|
FROM BASDEPARTMENT WHERE DEPARTMENTID = @DEPARTMENTID
|
|
|
|
UPDATE BPM_FLOWDATA_D SET SIGNPERSON = ISNULL(@BOSS_PERSON,'')
|
|
WHERE BILLNO = @G_BILLNO AND SEQNO = @SEQ AND SUBSTRING(@ORG_GROUP,1,7) = 'DEPBOSS' AND (FLOWSTATUS = 0 OR FLOWSTATUS = 2)
|
|
END
|
|
|
|
SET @PRE_PERSON = @BOSS_PERSON
|
|
SET @PRE_BILLNO = @BILLNO
|
|
|
|
FETCH NEXT FROM contact_cursor3
|
|
INTO @BILLNO,@SEQ,@SIGNPERSON,@ORG_GROUP,@DEPARTMENTID
|
|
END
|
|
|
|
CLOSE contact_cursor3
|
|
DEALLOCATE contact_cursor3
|