CREATE PROC DBO.GEXBPM_REJECT
(
  @BILLNO      VARCHAR(20),  --簽核單號
  @LOGINID     VARCHAR(30),
  @SIGNMEMO    NVARCHAR(600)
)
AS
/*
  Function   : FLOW 拒簽時的更新資料程序(直接否決本單)
  Description: 
    1.在原單或簽核待辦單上, 新增 [簽核拒簽] button (Reject)
      寫一筆資料到 log
      將 FLOWDATA_M 的 FLOWSTATUS 改為 7 拒簽
    2.原單的 FLOWFLAG = X, SIGN_USER = 拒簽
      
  Build Date : 2013/07/08
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2011/10/12 新增
*/

DECLARE @SQL NVARCHAR(2000)
DECLARE @SOURCETBL VARCHAR(100)
DECLARE @SOURCEPK  VARCHAR(100)
DECLARE @BILLTAG   VARCHAR(100)
DECLARE @SOURCEBILLNO VARCHAR(100)

DECLARE @PRE_DATE DATETIME
DECLARE @SEQ1 INT
DECLARE @SEQ2 INT
SELECT @SEQ1=MAX(SEQNO)+1,@SEQ2=MIN(SEQNO),@PRE_DATE=MAX(BILLDATE) FROM BPM_FLOWDATA_LOG WHERE BILLNO = @BILLNO
  
DECLARE @FLOW INT
SELECT @FLOW=FLOWSTATUS FROM BPM_FLOWDATA_D WHERE BILLNO = @BILLNO AND SEQNO = @SEQ2

IF @FLOW = 0 
BEGIN
  --寫 LOG
  INSERT INTO BPM_FLOWDATA_LOG (BILLNO,SOURCESEQNO,SEQNO,FLOWINDATE,BILLDATE,FLOWSTATUS,SIGNPERSON,MEMOCHAR)
  SELECT @BILLNO,@SEQ2,@SEQ1,@PRE_DATE,GETDATE(),7,@LOGINID,@SIGNMEMO

  --將 BPM_FLOWDATA_M 狀態改為 [7-拒簽]
  UPDATE BPM_FLOWDATA_M SET FLOWSTATUS = 7 WHERE BILLNO = @BILLNO

  --回寫原單 SIGN_USER
  SELECT @BILLTAG=BILLTAG,@SOURCEBILLNO=SOURCEBILLNO FROM BPM_FLOWDATA_M WHERE BILLNO = @BILLNO
  SELECT @SOURCETBL=SOURCETABLE,@SOURCEPK=SOURCEPK FROM BPM_FLOWSETUP_M WHERE BILLTAG = @BILLTAG
  
  SET @SQL = 'UPDATE ' + @SOURCETBL + ' SET SIGN_USER = ' + CHAR(39)+'拒簽'+CHAR(39) + ',FLOWFLAG = ' + CHAR(39)+'R'+CHAR(39) + 
             ' WHERE ' + @SOURCEPK + ' = ' + CHAR(39) + @SOURCEBILLNO + CHAR(39)
  EXECUTE SP_EXECUTESQL @SQL
  
  --INSERT INTO BPM_SENDMAIL (LOGINID,SENDDATE,SENDWHO,NOTIFY_ONLY,BILLNO,SEQNO)
  --SELECT @LOGINID,GETDATE(),@LOGINID,'F',@BILLNO,@SEQ2

  --應通知先前有簽核過的人
  --sql db sendmail 
  EXEC GEX_SEND_FLOWMAIL 'R',@LOGINID, @BILLNO
END