4/1健保調漲
% 調還好, 但加了補助就麻煩了
加了補助還好, 但是補助中間最多人就麻煩了
因為改了% 算出來後要調的人太多了
新版ERP有更新程式
但舊版的怎辦?
孤兒怎辦?

這兒所談的ERP系統是指鼎新WORKFLOW ERP
屬於 鼎新電腦版權所有!
不用怕 以下觸發可以幫您更改
**注意

1. 記得要更改 Leader 公司別 喔!

2. 要匯入 DSCSYS 資料庫喔!

3. 有多個公司別 就複製多個吧! 給不同名字!**

   DROP TRIGGER [trgYJOBQPALB05]
   GO

   CREATE TRIGGER [trgYJOBQPALB05] ON [dbo].[JOBQUEUE]
   FOR UPDATE
   AS

   DECLARE @JOBNAME char(20)
   DECLARE @STATUS char(1)
   DECLARE @COMPANYID varchar(100)
   DECLARE @MA022 varchar(6)
   DECLARE @TM001 varchar(10)
   DECLARE @TM010 varchar(10)
   DECLARE @TM012 numeric(5)
   DECLARE @TM014 numeric(2)
   DECLARE @A numeric(13,5)
   DECLARE @B numeric(13,5)
   DECLARE @C numeric(13,5)
   -- % 數
   SET @A=0.0455
   SET @B=0.0517
   SET @C=0.3

   SELECT @JOBNAME=JOBNAME, @STATUS=STATUS, @COMPANYID=COMPANYID FROM Inserted

   --Leader 公司別
   IF (@JOBNAME='PALB05' OR @JOBNAME='PALB03') AND @STATUS='D' AND @COMPANYID='Leader'
   BEGIN
   SELECT @MA022=MA022 FROM Leader..CMSMA
   SELECT @B=MM006, @C=MM007 FROM Leader..PALMM WHERE MM001='1'

   DECLARE PALTMC CURSOR FOR
   SELECT TM001 FROM Leader..PALTM WHERE (TM019 = '1') AND TM002=@MA022 ORDER BY  TM001
   OPEN PALTMC
   FETCH NEXT FROM PALTMC
   INTO @TM001
   WHILE   @@FETCH_STATUS = 0
   BEGIN
   

   SELECT @TM010=TM010,@TM012=TM012, @TM014=TM014
   FROM Leader..PALTM B
   WHERE (TM019 = '1') AND (TM001 = @TM001) AND (TM002 = @MA022)
   IF @TM014 <> 0
   BEGIN
   SELECT @TM012=@TM012-SUM(TN007)
   FROM Leader..PALTN A
   WHERE (TN001 = @TM001) AND (TN002 = @MA022)
   END
   IF @TM010 <= 40100
   BEGIN
   UPDATE A
   SET TN007=ROUND(TN007/ROUND(@TM010*@B*@C,0) * ROUND(@TM010*@A*@C,0),0)
   FROM Leader..PALTN A
   WHERE TN001 = @TM001 AND TN002 = @MA022
   SET @TM012=ROUND(@TM012/ROUND(@TM010*@B*@C,0) * ROUND(@TM010*@A*@C,0),0)
   END
   IF @TM010 > 40100 AND @TM010 <= 50600
   BEGIN
   UPDATE A
   SET TN007=ROUND(TN007/ROUND(@TM010*@B*@C,0) * (ROUND(@TM010 * @B * @C, 0) - ROUND((ROUND(@TM010 * @B * @C, 0) - ROUND(@TM010 * @A * @C, 0)) * 0.2, 0)),0)
   FROM Leader..PALTN A
   WHERE TN001 = @TM001 AND TN002 = @MA022
   SET @TM012=ROUND(@TM012/ROUND(@TM010*@B*@C,0) * (ROUND(@TM010 * @B * @C, 0) - ROUND((ROUND(@TM010 * @B * @C, 0) - ROUND(@TM010 * @A * @C, 0)) * 0.2, 0)),0)
   END

   IF @TM014 <> 0
   	BEGIN
   	SELECT         @TM012=SUM(TN007)+ @TM012
   	FROM             Leader..PALTN A
   	WHERE         (TN001 = @TM001) AND (TN002 = @MA022)
   END
   
   	UPDATE A 
   	SET TM012=@TM012
   	FROM Leader..PALTM A 
   	WHERE TM002=@MA022 AND TM001=@TM001
   IF @JOBNAME='PALB03'
   BEGIN
   	UPDATE A
   	SET TI033=@TM012, TI041=TI041+TI033-@TM012
   	FROM Leader..PALTI A
   	WHERE TI001=@TM001 AND TI002=@MA022
   END
       FETCH NEXT FROM PALTMC
       INTO @TM001
   END
   CLOSE PALTMC
   DEALLOCATE PALTMC
   

   END