IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[SPA_DEPREM_C]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [SPA_DEPREM_C]
GO
  
  
/*******************************************************    
--TYPE: SPECIALLY DESIGNATED IN ONE COUNTRY (CHINA)    
--DETAIL: china monthly depreciation     
--CREATE: 2001/11/06    
--REVISION1: SHIN MI RAN    
--REVISION2: yearly,monthly depreciation update    
--REVISION3 KWONTAEHWAN @DPRE_ST, @RTN_AM RE SET  

--REVISION2009 wanchengjian 对折旧年限和净残值率变动的处理  20090520
--REVISION2010 WANCHENGJIAN 折旧包含年月的时候,最后一年的时候折旧的计算有错误,
--REVISION2010/07/16  删除成本增加的变动后,当年年初的原值处理不正确.
 --20110214    折旧状态的处理暂时去掉了,否则年限延长后不能正常折旧.
 --20110818    部门移动,ADPREM表中的部门值,变动之前的保留之前的部门编码,变动后的使用新的部门编码值.
 --20120110    正好是上一年12月结束的资产,计算折旧是会出现被0除的问题,增加label  zero: 跳出循环
 --20120121    对资产变卖的支持更好,年度结转处理的时候下一年有资产减少变动的不用删除后再输入,同样能计算正确
 --20120726    支持上年12月折旧变动
********************************************************/    
-- SELECT * FROM #ASSET_TEMP_D
-- SELECT * FROM 
-- EXEC SPA_DEPREM_C '1000',5,'20120101','20121231','778', 1,1

--CREATE    PROC SPA_DEPREM_C     -- ERPX的版本的话需要把DBO去掉          
CREATE    PROC SPA_DEPREM_C      -- ERPX的版本的话需要把DBO去掉 

        @CO_CD            VARCHAR(4), -- company code      
        @SCO_GISU       NUMERIC(3),     -- the fiscal year      
        @FR_DT             VARCHAR(8), -- opening date fiscal year    
        @TO_DT             VARCHAR(8), -- fiscal year of closing day       
        @ASSET_CD       VARCHAR(10),--,      
        @DPRE_ST         VARCHAR(1) OUTPUT,      
        @RTN_AM           NUMERIC(17,4) OUTPUT      
      
----WITH ENCRYPTION      
AS      
    SET NOCOUNT ON      
    SET ANSI_WARNINGS OFF      
    
DECLARE          
        @FROMM        INT,  -- first month of fiscal year      
        @ENDM         INT,  --  last month of fiscal year      
        @ASSET_GETDT          VARCHAR(8),       -- acquire date      
        @ASSET_GETAM           NUMERIC(17,4),   -- acquire amount    
        @ASSET_CTDEPT         VARCHAR(4),      -- management division      
        @ASSET_PJTCD          VARCHAR(10),      -- PJT code      
        @ASSET_EXPENTY        VARCHAR(1),      -- cost classification    
        @ASSET_SLACK_AM       NUMERIC(17,4),    -- residual rate      
        @ASSET_DPRE_CNT       NUMERIC(17,4),    -- service life     
        @ADPREY_PRE_AM        NUMERIC(17,4),     -- amount of first book value fiscal year      
        @ASSET_D_CHG_AM       NUMERIC(17,4),     -- change amount     
        @ASSET_D_CHG_AM_Y     NUMERIC(17,4),     -- change amount of year    
        @ASSET_DPRE_FG        VARCHAR(1),       -- depreciation method      
        @MRAT                 NUMERIC(17,10),   -- monthly depreciation rate            
        @DPREAM_M             NUMERIC(17,4),    -- monthly general depreciation cost      
        @DEREAM_Y             NUMERIC(17,4),    -- yearly general depreciation cost      
        @ADPREY_PRE_DAM       NUMERIC(17,4),    -- former fiscal year allowance for depreciation      
        @ACCTERM              NUMERIC(17,4),    -- depreciatio term      
        @PREDAM_M             NUMERIC(17,4),    --  allowance for depreciation of former month     
        @POINT_AMT            INT,              -- amonut decimal point     
        @COUNT                INT,              -- count     
        @I                    INT,      
        @loop                 INT,      
        @DPRE_CNT_SUM         INT,      
        @DPRE_YM              VARCHAR(6),        --  Month YYYYMM      
        @CHG_YM               VARCHAR(6),        --  Month YYYYMM      
        @PREAM_TMP            NUMERIC(17,4),    -- first     
        @FLAG                 INT,                      -- this fiscal year or not      
        @CHG_FG               VARCHAR(2) ,     
        @CHG_FG_TMP           VARCHAR(2),  
--      @RTN_AM           NUMERIC(17,4),     
        @GETM   INT,      
        @RETROAM  NUMERIC(17,4),   
        @FRDPREM  VARCHAR(6),        
        @ADPREY_DPREMM  NUMERIC(3),      
        @ADPREY_GROWAM  NUMERIC(17,4),      
        @DEPREAM  NUMERIC(17,4),   
        @ADPREY_PREAM   NUMERIC(17,4),   
        @YYYY0                INT,  
        @YYYY1                INT,  
        @MM0                  INT,  
        @MM1                  INT,
        @STANDARD             NUMERIC(17,4),
        @DUMMY1               INT,
        @LLCOUNT INT,
        @LDPRE_YM VARCHAR(6),
        @GET_AFT NUMERIC(17,4),
        @GET_LAST NUMERIC(17,4) ,
        @LCCOUNT INT,
        @LLOOP INT
SET @LLOOP = 1
   
DECLARE @ASSET_D_CHG_DAM NUMERIC(17,4)
DECLARE @LYDPREAM_M NUMERIC(17,4)  

-->>>>>>>>>>>>>>>>>>>>>>>>>>>折旧年限变动的参数,
DECLARE @NYM INT,
        @NFLAG VARCHAR(1),
        @NCHG_DT VARCHAR(8),
        @NASSET_DPRE_CNT   NUMERIC(17,4), 
        @NDUMMY1   INT      
---->>>>>>>>>>>>>>>>>>>>>>>>>>>净残值率变动的参数,

DECLARE @SYM INT,
        @SFLAG VARCHAR(1),
        @SCHG_DT VARCHAR(8),
        @SASSET_SLACK_AM   NUMERIC(17,4)


BEGIN     
 --RETURN
 IF (SELECT CON_CD FROM SDBCFG) = 'CHS'   
 BEGIN   
  
  --BEGIN TRANSACTION     
  IF( @@ERROR <> 0 )    
  BEGIN    
     RAISERROR('BEGIN TRANSACTION ERROR[0]',16,1)    
     SET ANSI_WARNINGS ON    
     RETURN    
  END    
  DECLARE @FLAG1 VARCHAR(1) 
  
  SELECT  @FLAG1 = CASE WHEN SUBSTRING(CHG_DT,1,4) <  SUBSTRING(@FR_DT,1,4) AND CHG_FG = '2' THEN '1' ELSE '0' END
  FROM    ASSET_D WHERE ASSET_CD = @ASSET_CD   AND CO_CD = @CO_CD --判断是否已经变卖 
  SET  @FLAG1 = ISNULL(@FLAG1,'0')
    
  

  SET @RTN_AM = 0    
  SET @ENDM  = LEFT(@TO_DT, 4) * 12 + SUBSTRING(@TO_DT, 5, 2)    
  SET @CHG_FG = '-1'  
   
  -- DATA READ ON ASSET(ASSET) TABLE  
  SELECT  @ASSET_GETDT = A.GET_DT,    
          @ASSET_CTDEPT = A.CT_DEPT,    
          @ASSET_PJTCD = A.PJT_CD,    
          @ASSET_EXPENTY = A.EXPEN_TY,    
          @ASSET_DPRE_FG = A.DPRE_FG,    
          @ASSET_SLACK_AM =  ISNULL(A.SLACK_AM,0),    
          @ASSET_DPRE_CNT =  ISNULL(A.DPRE_CNT,0),    
          @ASSET_GETAM    =  ISNULL(A.GET_AM,0),  --ISNULL(A.GET_AM,0),    
          @ADPREY_PRE_AM  =  ISNULL(A.GET_AM,0),
          @DUMMY1 = ISNULL(CAST(DUMMY1 AS INT),0)     
     FROM ASSET A    
     --LEFT OUTER JOIN ADPREY Y ON A.CO_CD = Y.CO_CD AND A.ASSET_CD = Y.ASSET_CD AND Y.GISU = @SCO_GISU    
     WHERE A.CO_CD = @CO_CD  AND A.ASSET_CD = @ASSET_CD  
   
  --取以前年度的变动金额(原值) 不含12月
  DECLARE @CMONEY NUMERIC(17,4)
  SELECT @CMONEY = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0)    
                                          WHEN '1' THEN ISNULL(CHG_AM,0) * (-1)    
                                          WHEN '2' THEN 1 ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND SUBSTRING(CHG_DT,1,6) < CAST( (CAST(SUBSTRING(@FR_DT,1,4) AS INT) - 1 ) AS VARCHAR(4) ) + '12' 

  --取以前年度的变动金额(折旧) 不含12月
  DECLARE @CDEPREM NUMERIC(17,4)
  SELECT @CDEPREM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN 0 --ISNULL(CHG_AM,0)    
                                          WHEN '6' THEN 0 --ISNULL(CHG_AM,0) * (-1)    
                                          ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND SUBSTRING(CHG_DT,1,6) < CAST( (CAST(SUBSTRING(@FR_DT,1,4) AS INT) - 1 ) AS VARCHAR(4) ) + '12' 
  
  --把以前各期的变动原值考虑进去
  SELECT @ASSET_GETAM = ISNULL(@ASSET_GETAM,0) + ISNULL(@CMONEY,0)

    
  IF( @@ERROR <> 0 )    
  BEGIN    
          RAISERROR('ERROR OCCURED ON READING ASSET MASTER DATA[1]',16,1)    
          ROLLBACK TRANSACTION     
          SET ANSI_WARNINGS ON    
          RETURN    
  END    
    
  IF (@ASSET_GETAM <= 0 ) RETURN   -- CANNOT FIRST VALUE BELOW 0   
    
  SELECT @POINT_AMT = ISNULL(CTR_NB,0) FROM SYSCFG WHERE CO_CD = @CO_CD AND  CTR_CD = '06'  -- DECIMAL DIGIT    
  
 -- APPLICATE MONTHLY DEPRECIATION.. *******************************************************    
 -- DELETE CALCULATED MONTH DEPRECIATION    
  DELETE  ADPREM  WHERE CO_CD=@CO_CD  AND GISU=@SCO_GISU  AND ASSET_CD = @ASSET_CD    
  IF( @@ERROR <> 0 )    
  BEGIN    
          RAISERROR('ERROR OCCURED ON DELETING DATA OF MONTHLY DEPRECIATION ',16,1)    
          ROLLBACK TRANSACTION     
          SET ANSI_WARNINGS ON    
          RETURN    
  END
    
     -------------------------------------停用/启用固定资产------------------------------------------
     CREATE TABLE #ASSET_RESULT_D(YM VARCHAR(6),FLAG INT) --FLAG 0-表示计提折旧,1-表示不计提折旧
     IF (SELECT COUNT(*) 
         FROM ASSET_D 
         WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG IN ('3','4') 
         AND LEFT(CHG_DT,6) < = LEFT(@TO_DT,6) ) > 0
       BEGIN
        --处理固定资产停用/启用
        DECLARE @LCHG_DT VARCHAR(8),
                @LCHG_FG  VARCHAR(1),
                @LORDER INT
        
        --根据停用和启用进行匹配
        SET @LORDER = 1
        CREATE TABLE #ASSET_TEMP_D (ORDERID INT,STOP_DT VARCHAR(8),REUSE_DT VARCHAR(8))
                
        DECLARE CURSOR_ASSET CURSOR FOR
                SELECT CHG_DT,CHG_FG
                FROM   ASSET_D 
                WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG IN ('3','4') AND LEFT(CHG_DT,6) < = LEFT(@TO_DT,6)
                ORDER BY CHG_DT
        OPEN CURSOR_ASSET
        FETCH NEXT FROM CURSOR_ASSET INTO @LCHG_DT,@LCHG_FG
        WHILE @@FETCH_STATUS = 0
          BEGIN
            IF @LCHG_FG = '3'
               INSERT INTO #ASSET_TEMP_D(ORDERID,STOP_DT)
                                  VALUES(@LORDER,@LCHG_DT)
            IF @LCHG_FG = '4'
               BEGIN
                 UPDATE #ASSET_TEMP_D
                 SET    REUSE_DT = @LCHG_DT
                 WHERE  ORDERID = @LORDER
 
                 SET @LORDER = @LORDER + 1
               END
            FETCH NEXT FROM CURSOR_ASSET INTO @LCHG_DT,@LCHG_FG
          END        
        CLOSE CURSOR_ASSET
        DEALLOCATE CURSOR_ASSET
        --如果只有停用,没有启用某固定资产,就设置启用日期为@TO_DT(即到年底也没有启用) 
        UPDATE #ASSET_TEMP_D
        SET    REUSE_DT = @TO_DT
        WHERE  REUSE_DT IS NULL        
        --根据匹配的结果转化
        DECLARE @STOP_DT VARCHAR(8),
                @REUSE_DT VARCHAR(8)
                
        --CREATE TABLE #ASSET_RESULT_D(YM VARCHAR(6),FLAG INT) --FLAG 0-表示计提折旧,1-表示不计提折旧
        
        SELECT @LORDER = 0      
        DECLARE CURSOR_R_ASSET CURSOR FOR
                SELECT ORDERID,STOP_DT,REUSE_DT
                FROM   #ASSET_TEMP_D
                ORDER BY ORDERID
        OPEN CURSOR_R_ASSET
        FETCH NEXT FROM CURSOR_R_ASSET INTO @LORDER,@STOP_DT,@REUSE_DT
        WHILE @@FETCH_STATUS = 0
          BEGIN
            --停用当月计提折旧,停用月份的下一个月开始不提折旧,
            --启用当月不计提折旧,启用月份的下一个月开始计提折旧
            --如果如果购置月份和停用月份为同一个月的时候,停用当月不计体折旧
            INSERT INTO  #ASSET_RESULT_D (YM,FLAG)
                       SELECT YM,(CASE WHEN YM = LEFT(@STOP_DT,6) AND YM = LEFT(@ASSET_GETDT,6) THEN 0 ELSE 1 END)                                      
                       FROM   SYM
                       WHERE  YM BETWEEN LEFT(@STOP_DT,6) AND LEFT(@REUSE_DT,6)
                       ORDER  BY YM            --待处理问题...购置月的停用问题
            FETCH NEXT FROM CURSOR_R_ASSET INTO @LORDER,@STOP_DT,@REUSE_DT
          END
        CLOSE CURSOR_R_ASSET        
        DEALLOCATE CURSOR_R_ASSET
        SELECT @LLCOUNT = COUNT(*) FROM  #ASSET_RESULT_D WHERE  FLAG = 1 AND YM < LEFT(@FR_DT,6) -- 本年以前停提的月数     
        SELECT @LCCOUNT = COUNT(*) FROM  #ASSET_RESULT_D WHERE  FLAG = 1 AND YM BETWEEN LEFT(@FR_DT,6) AND LEFT(@TO_DT,6) -- 本年停提的月数
       END
       SET @LLCOUNT = ISNULL(@LLCOUNT,0)    
       --SELECT * FROM #ASSET_RESULT_D 
       --------------------------------停用/启用固定资产   处理结束-----------------------------------------   

   --FOR CHECKING LAST MONTH  
  IF (@FR_DT <= @ASSET_GETDT AND @TO_DT >= @ASSET_GETDT)    
    BEGIN    
         SET @FROMM = LEFT(@ASSET_GETDT, 4) * 12 + SUBSTRING(@ASSET_GETDT, 5, 2)    
         SET @FLAG = 0    
    END    
  ELSE -- ACQUIRE DATE IS NOT THIS FISCAL YEAR     
  BEGIN    
        IF ( @TO_DT < @ASSET_GETDT )    
        BEGIN    
          ROLLBACK TRANSACTION     
          RETURN    
        END    
        
        SET @YYYY0 = CONVERT(INT, LEFT(@FR_DT, 4))  
        SET @YYYY1 = CONVERT(INT, LEFT(@ASSET_GETDT, 4))  
        SET @MM0 = CONVERT(INT,SUBSTRING(@FR_DT,5,2))  
        SET @MM1 = CONVERT(INT,SUBSTRING(@ASSET_GETDT,5,2))  
        
        IF (@FR_DT > @ASSET_GETDT) 
             SELECT @COUNT = (@YYYY0 * 12 + @MM0) - (@YYYY1 * 12 + @MM1)  
        ELSE 
             SELECT @COUNT = 1+ ((@YYYY0 - @YYYY1 -1 ) * 12) +  (12 - @MM1) 
     
       SET @COUNT = ISNULL(@COUNT,0) - ISNULL(@LLCOUNT,0) --减去本会计年度以前的停用月数-------------------------------------------------------------- 

       -- SELECT ASSET_CD,@COUNT AS COUNT1 INTO T_TEMP_TABLE FROM ADPREY  SELECT * FROM T_TEMP_TABLE

       --20110214    折旧状态的处理暂时去掉了,否则年限延长后不能正常折旧.

       /*
       IF (@COUNT  > (@ASSET_DPRE_CNT) * 12 + @DUMMY1) AND 
                (NOT EXISTS (SELECT 1 FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND  CHG_DT BETWEEN @FR_DT AND @TO_DT AND CHG_FG = 2))
                 --超过使用期限的变卖    
        BEGIN    
             UPDATE ASSET  SET DPRE_ST  = '2' WHERE CO_CD    = @CO_CD  AND ASSET_CD = @ASSET_CD    
             IF( @@ERROR <> 0 )    
             BEGIN    
                RAISERROR('ERROR OCCURED ON ASSET MASER MODIFICATION[8]',16,1)    
                ROLLBACK TRANSACTION     
                SET ANSI_WARNINGS ON    
                RETURN    
             END    
             --COMMIT TRANSACTION     
             RETURN    
        END    
       */ 
       SET @FROMM = LEFT(@FR_DT, 4) * 12 + SUBSTRING(@FR_DT, 5, 2)    
       SET @FLAG = 1    
       IF ((@ASSET_DPRE_CNT) * 12 + @DUMMY1 - @COUNT  < 12)   
          BEGIN 
            SET @ENDM  = LEFT(@TO_DT, 4) * 12 + SUBSTRING(@ASSET_GETDT, 5, 2) + @DUMMY1 --+ @LCCOUNT
            IF  @ENDM - @FROMM >12 
                SET @ENDM = @ENDM - 12      -- DUMMY1 有月份的年限的时候,最有一年折旧的时候,这个@ENDM的计算会超出12个月... 
          END
  END    
    
  IF (@COUNT IS NULL) SET @COUNT = 1    
    
  --  FIRST MONTH DEPRECIATION= FIRST MONTH OF FIXCAL YEAR( WHEN REGISTRATE ASSET OF LAST FISCAL YEARLY )         
  --  CALCULATE DEPRECIATION RATE       
  IF (@ASSET_DPRE_FG = '1') --FIXED AMOUNT (1-RESIDUAL)/(DEPRECIATION TERMS*12)         
      SET @MRAT = ROUND((1-@ASSET_SLACK_AM/100) / (@ASSET_DPRE_CNT*12 + @DUMMY1),6)   -- MONTHLY RATE  
  ELSE 
  IF (@ASSET_DPRE_FG = '2') -- SUM OF YEARS DIGITS  
    BEGIN    
        SELECT  @LOOP = @ASSET_DPRE_CNT, @DPRE_CNT_SUM = 0, @I = 0    
        WHILE(@LOOP > 0)    
          BEGIN    
             SET @DPRE_CNT_SUM = @DPRE_CNT_SUM + (@ASSET_DPRE_CNT - @I)    
             SELECT  @LOOP = @LOOP - 1, @I = @I + 1    
          END    
              
        SET @MRAT = ROUND((@ASSET_DPRE_CNT / @DPRE_CNT_SUM)/12,6)    
    END    
    
  SET @ACCTERM = @ENDM - @FROMM + 1                                     -- NUMBER OF MONTH DEPRECIATION                    
     
  SET @I = @FROMM  - @FLAG    
  SET @PREDAM_M = 0    
  SET @DEREAM_Y = 0    
  
  SELECT @ADPREY_PRE_DAM = ISNULL(PRE_DAM,0) --- ISNULL(DROP_DAM,0)
  FROM    ADPREY  WHERE CO_CD=@CO_CD AND ASSET_CD = @ASSET_CD AND GISU = @SCO_GISU    
  --把以前各期的折旧变动考虑进去
  SET @ADPREY_PRE_DAM = @ADPREY_PRE_DAM + ISNULL(@CDEPREM,0)

  WHILE( @I < @ENDM )     --+ ISNULL(@LCCOUNT,0)
  BEGIN 
      
       IF (@ASSET_DPRE_FG = '0') --1.DUPLICATE DEPRECIATION DIGITS 2/(*12)     2. BEFORE 2YEAR EQUAL TO FIXED AMOUNT METHOD    
       BEGIN  
           IF (@CHG_FG = '2') 
               SELECT @MRAT = 0 ,@ASSET_DPRE_FG = '2'  
           ELSE 
               IF (@COUNT >= (@ASSET_DPRE_CNT*12 + @DUMMY1) - 24)   
                   SET @MRAT = ROUND((1 - @ASSET_SLACK_AM/100) / (@ASSET_DPRE_CNT*12+@DUMMY1),6)   -- MONTH INTEREST RATE     
               ELSE 
                   SET @MRAT = ROUND((2 / @ASSET_DPRE_CNT)/12,6)   -- MONTH INTEREST RATE 
           
       END     
       -- CHECKING LAST MONTH    
       -- LAST MONTH = FIRST BOOK VALUE - ALLOWANCE FOR DEPRECIATION     
       -- NOT PRESENTATION DEPRECIATION RATE(NO USE) 

       -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>年限变动以后,对最后一个月的判断有影响
       SELECT @NASSET_DPRE_CNT = @ASSET_DPRE_CNT,@NDUMMY1 = @DUMMY1

       SELECT @NCHG_DT = NULLIF('',''),@NYM = 0,@NFLAG = '0'  
       SELECT @NCHG_DT = MAX(CHG_DT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = '9' AND LEFT(CHG_DT,6) < LEFT(@TO_DT,6)

       IF ISNULL(@NCHG_DT,'00000000') <> '00000000'
          BEGIN
             IF @LLOOP = 1
                BEGIN
                  SELECT @NYM = CAST(ROUND(CHG_AM,0) AS INT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = '9' AND CHG_DT = @NCHG_DT
                                                                
                  SELECT @NASSET_DPRE_CNT = ROUND(@NYM/12,0,1),@NDUMMY1 = @NYM - ROUND(@NYM/12,0,1)*12
             
                  -- 年限变动后折旧结束月份的处理
                  SET @ENDM  = LEFT(@TO_DT, 4) * 12 + SUBSTRING(@TO_DT, 5, 2)             
                  --SET @ENDM  = LEFT(@TO_DT, 4) * 12 + SUBSTRING(@ASSET_GETDT, 5, 2) + @NDUMMY1
             
                  IF ((@NASSET_DPRE_CNT) * 12 + ISNULL(@NDUMMY1,0) - @COUNT  < 12)   
                     BEGIN 
                       SET @ENDM  = LEFT(@TO_DT, 4) * 12 + SUBSTRING(@ASSET_GETDT, 5, 2) + @NDUMMY1 --+ @LCCOUNT
                   
                       IF  @ENDM - @FROMM >12 
                            SET @ENDM = @ENDM - 12      -- 变动后的年限有月份的时候 
                     END
                  SET @LLOOP = 2
                END  -- END OF IF @LLOOP = 1
          END 
       
       SELECT @SASSET_SLACK_AM = @ASSET_SLACK_AM
       
       SELECT @SCHG_DT = NULLIF('',''),@SYM = 0  
       SELECT @SCHG_DT = MAX(CHG_DT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = 'A' AND LEFT(CHG_DT,6) < LEFT(@TO_DT,6)
       IF ISNULL(@SCHG_DT,'00000000') <> '00000000'
          BEGIN
             SELECT @SYM = CAST(ROUND(CHG_AM,0) AS INT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = 'A' AND CHG_DT = @SCHG_DT
             SELECT @SASSET_SLACK_AM = @SYM
          END 
                 
       IF (@COUNT = (@NASSET_DPRE_CNT*12) + @NDUMMY1)    
         BEGIN    --最后一个月计体折旧的计算   >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
            SELECT @ADPREY_PRE_DAM = 0,@ASSET_D_CHG_DAM = 0
            SELECT @ADPREY_PRE_DAM = ISNULL(PRE_DAM,0)  
            FROM   ADPREY  
            WHERE  CO_CD=@CO_CD  AND ASSET_CD = @ASSET_CD AND GISU = @SCO_GISU 
             
             
            SELECT @ASSET_D_CHG_DAM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN  ISNULL(CHG_AM,0)    
                                                          WHEN '6' THEN  ISNULL(CHG_AM,0) * (-1)    
                                                          ELSE 0 END),0)                
            FROM  ASSET_D     
            WHERE CO_CD     = @CO_CD     
            AND   ASSET_CD  = @ASSET_CD     
            AND   CHG_DT BETWEEN @FR_DT AND @TO_DT
            
            
            SET @DPRE_YM = STR(@I / 12, 4) + RIGHT(STR(@I % 12 + 101, 3), 2)   
 
            SET @GET_AFT = 0
           
            SELECT @GET_AFT = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN  ISNULL(CHG_AM,0)    
                                                     WHEN '1' THEN  ISNULL(CHG_AM,0) * (-1)    
                                                     ELSE 0 END),0)                
            FROM  ASSET_D     
            WHERE CO_CD     = @CO_CD     
            AND   ASSET_CD  = @ASSET_CD     
            AND   CHG_DT BETWEEN @FR_DT AND @TO_DT --AND LEFT(CHG_DT,6) > @DPRE_YM     
            
                        
            SET @ASSET_GETAM = ISNULL(@ASSET_GETAM,0) + ISNULL(@GET_AFT,0)
            
            
            SET   @ADPREY_PRE_DAM = ISNULL(@ADPREY_PRE_DAM,0) + ISNULL(@ASSET_D_CHG_DAM,0)          

            SET @MRAT = 0    
            SET @DPREAM_M = ROUND((@ASSET_GETAM - (@ASSET_GETAM*(@SASSET_SLACK_AM/100)))-(@ADPREY_PRE_DAM + @DEREAM_Y),ISNULL(@POINT_AMT,0))    
            
            SET @DPRE_YM = STR(@I / 12, 4) + RIGHT(STR(@I % 12 + 101, 3), 2)    
         END                
       ELSE -- IF @COUNT <= (@NASSET_DPRE_CNT*12)+@NDUMMY1   -------------------------
       BEGIN    
         --PRINT 12345
         SET @DPRE_YM = STR(@I / 12, 4) + RIGHT(STR(@I % 12 + 101, 3), 2)    
         SET @CHG_YM  = STR((@I-1) / 12, 4) + RIGHT(STR((@I-1) % 12 + 101, 3), 2)    
         --=====================================================================================================================
         --对于资产变动的处理(变动金额考虑进去)*********原值的变动 折旧的变动
         SELECT @ASSET_D_CHG_AM = 0,@ASSET_D_CHG_DAM = 0
         /*
         SELECT @ASSET_D_CHG_AM = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0)    
                                                       WHEN '1' THEN ISNULL(CHG_AM,0) * (-1)    
                                                       WHEN '2' THEN 1 ELSE 0 END),0),   
                @ASSET_D_CHG_DAM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN  ISNULL(CHG_AM,0)    
                                                          WHEN '6' THEN  ISNULL(CHG_AM,0) * (-1)    
                                                          ELSE 0 END),0),         
                @CHG_YM = LEFT(MAX(CHG_DT),6),                         
                @CHG_FG_TMP =  MAX(CHG_FG)  
         FROM  ASSET_D     
         WHERE CO_CD     = @CO_CD     
         AND   ASSET_CD  = @ASSET_CD     
         AND   CHG_DT LIKE @CHG_YM +'%'
         */
         --原值变动
         SELECT @ASSET_D_CHG_AM = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0)    
                                                       WHEN '1' THEN ISNULL(CHG_AM,0) * (-1)    
                                                       WHEN '2' THEN 1 ELSE 0 END),0)                       
               
         FROM  ASSET_D     
         WHERE CO_CD     = @CO_CD     
         AND   ASSET_CD  = @ASSET_CD     
         AND   CHG_DT LIKE @CHG_YM +'%'
         --折旧变动
         SELECT @ASSET_D_CHG_DAM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN  ISNULL(CHG_AM,0)    
                                                          WHEN '6' THEN  ISNULL(CHG_AM,0) * (-1)    
                                                          ELSE 0 END),0)                
         FROM  ASSET_D     
         WHERE CO_CD     = @CO_CD     
         AND   ASSET_CD  = @ASSET_CD     
         AND   CHG_DT LIKE @CHG_YM +'%' AND CHG_DT BETWEEN @FR_DT AND @TO_DT  /*折旧取的是年初折旧,所以上年12月的不要考虑*/   
         --变动标识
         SELECT @CHG_YM = LEFT(MAX(CHG_DT),6),                         
                 @CHG_FG_TMP =  MAX(CHG_FG)  
         FROM  ASSET_D     
         WHERE CO_CD     = @CO_CD     
         AND   ASSET_CD  = @ASSET_CD     
         AND   CHG_DT LIKE @CHG_YM +'%'           
            
         --------------
         IF (@CHG_FG_TMP IS NOT NULL ) SET @CHG_FG = @CHG_FG_TMP  
   
         IF (@ASSET_D_CHG_AM <> 0 OR @ASSET_D_CHG_DAM <> 0)    
            BEGIN    
                   -- APPLICATE NEXT DAY FOR CHANGE OCCURED    
                   IF (@CHG_FG = '2')   
                    BEGIN   
                       SET @ASSET_GETAM = 0   
                       SET @MRAT = 0   
                       SET @ASSET_DPRE_FG = 2  
                       SET @DEREAM_Y = 0   
                       UPDATE ASSET  SET DPRE_ST  = '2'   
                       WHERE CO_CD    = @CO_CD  AND ASSET_CD = @ASSET_CD    
                    END  
                   ELSE --变动的原值和折旧,在折旧计算的源数据中能体现.
                     SELECT @ASSET_GETAM = ISNULL(@ASSET_GETAM,0) + CASE WHEN @DPRE_YM > @CHG_YM THEN  ISNULL(@ASSET_D_CHG_AM,0) 
                                                                         ELSE 0 END,
                            @ADPREY_PRE_DAM = ISNULL(@ADPREY_PRE_DAM,0) + CASE WHEN @DPRE_YM > @CHG_YM THEN  ISNULL(@ASSET_D_CHG_DAM,0) 
                                                                               ELSE 0 END                         
             END     
                         
         IF (@ASSET_GETAM < 0) SET @ASSET_GETAM = 0       
    
         SET @ADPREY_PRE_AM = @ADPREY_PRE_AM - @DPREAM_M      
  
    
       IF (@ASSET_DPRE_FG = 2) --年数总和法  
         IF (@CHG_FG = '2')   
          BEGIN  
             SET @DPREAM_M = 0  
             SET @DEREAM_Y = 0  
          END  
         ELSE  --IF (@CHG_FG = 2)
          SET @DPREAM_M =ROUND((@ASSET_GETAM -  CASE @ASSET_DPRE_FG WHEN '2' THEN (@ASSET_GETAM * @ASSET_SLACK_AM/100) 
                                                                    ELSE 0 END) *@MRAT,ISNULL(@POINT_AMT,0),0)   -- 월상각    
  
        ELSE   --IF (@ASSET_DPRE_FG = 2) 
            -- PRINT 'TEST'
            BEGIN --平均年限法计算折旧的公式  SELECT * FROM ADPREM  SELECT CAST(LEFT('200601',4) - 1 AS VARCHAR(4)) + '12'
             -- PRINT 'TEST1'   OK
              IF @DPRE_YM NOT IN (SELECT YM FROM #ASSET_RESULT_D WHERE FLAG = 1)  ------------------------------------------ 
                 BEGIN 
                  -- PRINT 'TEST1' OK
                  -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                  --查找当前月份之前有没有折旧年限的变动
                  --
                  SELECT @NCHG_DT = NULLIF('',''),@NYM = 0,@NFLAG = '0'  
                  SELECT @NCHG_DT = MAX(CHG_DT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = '9' AND LEFT(CHG_DT,6) < @DPRE_YM
                  IF ISNULL(@NCHG_DT,'00000000') <> '00000000'
                     BEGIN
                       SELECT @NYM = CAST(ROUND(CHG_AM,0) AS INT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = '9' AND CHG_DT = @NCHG_DT

                       SELECT @ASSET_DPRE_CNT = ROUND(@NYM/12,0,1),@DUMMY1 = @NYM - ROUND(@NYM/12,0,1)*12                       
                     END    

                  -- PRINT 'TEST1' OK
                  
                  SELECT @SCHG_DT = NULLIF('',''),@SYM = 0  
                  SELECT @SCHG_DT = MAX(CHG_DT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = 'A' AND LEFT(CHG_DT,6) < @DPRE_YM
                  IF ISNULL(@SCHG_DT,'00000000') <> '00000000'
                  BEGIN
                    SELECT @SYM = CAST(ROUND(CHG_AM,0) AS INT) FROM ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_FG = 'A' AND CHG_DT = @SCHG_DT
                    SELECT @ASSET_SLACK_AM = @SYM
                  END 

                  --PRINT 'TEST1' OK

                  -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                  IF @ASSET_DPRE_CNT*12 + @DUMMY1 - (@COUNT-1) <= 0  GOTO ZERO
                  
                  -- PRINT 'TEST1' ERROR 

                  SET @DPREAM_M = ROUND((@ASSET_GETAM - (@ASSET_GETAM * @ASSET_SLACK_AM/100) - ISNULL(@ADPREY_PRE_DAM,0))  
                                         /(@ASSET_DPRE_CNT*12 + @DUMMY1 - (@COUNT-1)),ISNULL(@POINT_AMT,0),0 ) --0-四舍五入 ,1就是截位

                  --PRINT @ASSET_CD

                  --处理相同区间断折旧额一致的问题
                  SELECT @LYDPREAM_M = 0
                  DECLARE @LLLDPRE_YM NVARCHAR(12)
                  SELECT @LLLDPRE_YM = CASE WHEN RIGHT(@DPRE_YM,2) = '01' THEN CAST(LEFT(@DPRE_YM,4) - 1 AS NVARCHAR(12)) + '12'
                                          ELSE  @DPRE_YM - 1 END
                  IF NOT EXISTS(SELECT 1 FROM ASSET_D WHERE ASSET_CD = @ASSET_CD AND CHG_FG IN ('0','1','2','5','6')
                                                      --AND LEFT(CHG_DT,6) < @DPRE_YM AND CO_CD = @CO_CD)
                                                      AND LEFT(CHG_DT,6) = @LLLDPRE_YM AND CO_CD = @CO_CD)
                   BEGIN                               
                     SELECT @LYDPREAM_M = CUR_DAM FROM ADPREM 
                                               WHERE ASSET_CD = @ASSET_CD AND DPRE_YM = CAST(LEFT(@DPRE_YM,4)-1 AS VARCHAR(4)) + '12' AND CO_CD = @CO_CD
                     SELECT @LYDPREAM_M = ISNULL(@LYDPREAM_M,0)
                   END
                  IF SUBSTRING(@DPRE_YM,5,2) = '01' AND @LYDPREAM_M <> 0
                     SET @STANDARD = @LYDPREAM_M  --如果以前没有变动的话,取上一年最后一个月的值 
                  IF SUBSTRING(@DPRE_YM,5,2) = '01' AND @LYDPREAM_M = 0
                     SET @STANDARD = @DPREAM_M  --每年的第一个月
                  -------------------------------------------------------------------
                  SELECT @LDPRE_YM = MAX(YM) FROM SYM WHERE YM < @DPRE_YM
                  
                  IF @ASSET_D_CHG_AM <> 0 OR @ASSET_D_CHG_DAM <> 0 
                       OR EXISTS(SELECT 1 FROM ASSET_D WHERE LEFT(CHG_DT,6) = @LDPRE_YM AND ASSET_CD = @ASSET_CD AND CHG_FG = '4' AND CO_CD = @CO_CD) 
                       OR EXISTS(SELECT 1 FROM ASSET_D WHERE LEFT(CHG_DT,6) = @LDPRE_YM AND ASSET_CD = @ASSET_CD AND CHG_FG = '9' AND CO_CD = @CO_CD)
                       OR EXISTS(SELECT 1 FROM ASSET_D WHERE LEFT(CHG_DT,6) = @LDPRE_YM AND ASSET_CD = @ASSET_CD AND CHG_FG = 'A' AND CO_CD = @CO_CD)
                     SET @STANDARD = @DPREAM_M  --出现变动的话,这次变动到下次变动的折旧额应该一致                  
                  
                  IF @COUNT = 1 SET  @STANDARD = @DPREAM_M  --如果是第一次计提折旧
                  
                  
                  IF @STANDARD <> @DPREAM_M 
                     SET @DPREAM_M = @STANDARD  --如果出现一个区间断内折旧额不一致时,以标准的折旧额为正确的值
                     
                  SET @ADPREY_PRE_DAM = @ADPREY_PRE_DAM + @DPREAM_M

                  --PRINT 'TEST1' ERROR 
                 END
              IF @DPRE_YM IN (SELECT YM FROM #ASSET_RESULT_D WHERE FLAG = 1) --如果停用了就删除相应月份的折旧记录
                 DELETE FROM ADPREM WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND DPRE_YM = @DPRE_YM
              
            END  
       END 
     --PRINT 'TEST1'    
     IF @FLAG1 = '0' AND @DPRE_YM NOT IN (SELECT YM FROM #ASSET_RESULT_D WHERE FLAG = 1)  
         -- AND @COUNT <= (@NASSET_DPRE_CNT*12)+@NDUMMY1 --未变卖和当月不是因停用而不计提折旧的月份---
         INSERT INTO ADPREM( CO_CD,ASSET_CD,GISU,DPRE_YM,PRE_AM,GROW_AM,DROP_AM,PRE_DAM,CUR_DAM, CT_DEPT,     
                             PJT_CD,EXPEN_TY,DUMMY1, DUMMY2 )    
                     VALUES( @CO_CD,@ASSET_CD, @SCO_GISU,@DPRE_YM,@ADPREY_PRE_AM,     
                             0,0 ,@PREDAM_M, @DPREAM_M, @ASSET_CTDEPT,     
                             @ASSET_PJTCD,@ASSET_EXPENTY, @MRAT*100, STR(@COUNT) )    
      IF( @@ERROR <> 0 )    
      BEGIN    
               RAISERROR('ERROR OCCURED ON CREATION MONTHLY DEPREATION DATA[6]',16,1)    
               ROLLBACK TRANSACTION     
               SET ANSI_WARNINGS ON    
               RETURN    
      END    
     IF @DPRE_YM NOT IN (SELECT YM FROM #ASSET_RESULT_D WHERE FLAG = 1) --当月不是因停用而不计提折旧的月份---
      BEGIN
       SET @DEREAM_Y = @DEREAM_Y + @DPREAM_M     
       SET @COUNT    = @COUNT + 1    
       SET @PREDAM_M = @PREDAM_M + @DPREAM_M           
       SET @RTN_AM = @RTN_AM+ @DPREAM_M    
       SET @ASSET_D_CHG_AM_Y = @ASSET_D_CHG_AM_Y + @ASSET_D_CHG_AM 
      END 
     SET @I = @I + 1 
  END  --WHILE( @I < @ENDM)     
  -- 部门变动的处理 ADPREM 表中变动前的月份保留原来的部门,变动后用新的部门编码 
  -- 一个会计年度支持一次部门变动
  -- SELECT * FROM ASSET_D WHERE ASSET_CD = '10001'  SELECT * FROM ADPREM WHERE  ASSET_CD = '10001'
  DECLARE @OLDER_DEPT NVARCHAR(5),
          @DEPT_CHGDT NVARCHAR(8)
  SELECT @OLDER_DEPT = PRECH_CD,@DEPT_CHGDT = CHG_DT
  FROM   ASSET_D
  WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_DT BETWEEN @FR_DT AND @TO_DT AND CHG_FG = '7'
  IF EXISTS (SELECT 1 FROM   ASSET_D
                      WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_DT 
                      BETWEEN @FR_DT AND @TO_DT AND CHG_FG = '7')
     UPDATE ADPREM
     SET    CT_DEPT = @OLDER_DEPT
     WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND DPRE_YM >= LEFT(@FR_DT,6) AND DPRE_YM < LEFT(@DEPT_CHGDT,6)
 
  --变动的金额更新到ADPREM 和 ADPREY 表中的处理*************************************************************************
  -- SELECT * FROM T_ASSET_D_TEMP
  -----------------------------------------------------------------------------------------------
  
  IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'T_ASSET_D_TEMP' AND XTYPE = 'U')
     DROP TABLE T_ASSET_D_TEMP

  SELECT A.ASSET_CD,A.CO_CD,A.CHG_YM,SUM(ISNULL(A.GROW_AM,0)) GROW_AM,SUM(ISNULL(A.DROP_AM,0)) DROP_AM,
         SUM(ISNULL(A.GROW_DAM,0)) GROW_DAM,SUM(ISNULL(A.DROP_DAM,0)) DROP_DAM
  INTO  T_ASSET_D_TEMP
  FROM 
  (SELECT (CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0) ELSE 0 END)  AS GROW_AM,
          (CASE CHG_FG WHEN '1' THEN ISNULL(CHG_AM,0)   
                       WHEN '2' THEN ISNULL(CHG_DAM,0) ELSE 0 END) AS DROP_AM,
          (CASE CHG_FG WHEN '5' THEN ISNULL(CHG_AM,0) ELSE 0 END) AS GROW_DAM,
          (CASE CHG_FG WHEN '6' THEN ISNULL(CHG_AM,0)   
                       WHEN '2' THEN ISNULL(CHG_DAM,0) ELSE 0 END) AS DROP_DAM,
          ASSET_CD,CO_CD,LEFT(CHG_DT,6) AS CHG_YM  --(CASE WHEN LEFT(CHG_DT,6) > @DPRE_YM THEN @DPRE_YM ELSE LEFT(CHG_DT,6) END) AS CHG_YM
   FROM   ASSET_D WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND CHG_DT BETWEEN @FR_DT AND @TO_DT ) A
   GROUP BY  A.ASSET_CD,A.CO_CD,A.CHG_YM
  
  UPDATE ADPREM
  SET    ADPREM.GROW_AM = T_ASSET_D_TEMP.GROW_AM,ADPREM.DROP_AM = T_ASSET_D_TEMP.DROP_AM,
         ADPREM.CUR_DAM =  ADPREM.CUR_DAM + T_ASSET_D_TEMP.GROW_DAM,         
         ADPREM.DROP_DAM = T_ASSET_D_TEMP.DROP_DAM
  FROM   T_ASSET_D_TEMP
  WHERE  ADPREM.CO_CD = T_ASSET_D_TEMP.CO_CD  AND ADPREM.ASSET_CD = T_ASSET_D_TEMP.ASSET_CD
  AND    ADPREM.ASSET_CD = @ASSET_CD AND  ADPREM.DPRE_YM = T_ASSET_D_TEMP.CHG_YM

  

  IF EXISTS (SELECT 1 FROM T_ASSET_D_TEMP WHERE CHG_YM > @DPRE_YM )
     INSERT INTO ADPREM(CO_CD,ASSET_CD,GISU,DPRE_YM,GROW_AM,DROP_AM,GROW_DAM,DROP_DAM)
                 SELECT CO_CD,ASSET_CD,@SCO_GISU,CHG_YM,GROW_AM,DROP_AM,GROW_DAM,DROP_DAM
                 FROM   T_ASSET_D_TEMP WHERE CHG_YM > @DPRE_YM
 -- SELECT * FROM ADPREM
  
  -----------
  -- 一年内两次折旧的变化会出现问题
  /*
  UPDATE ADPREM
  SET    ADPREM.PRE_DAM  = CASE WHEN ADPREM.DPRE_YM > T_ASSET_D_TEMP.CHG_YM 
                              THEN ADPREM.PRE_DAM + T_ASSET_D_TEMP.GROW_DAM ELSE ADPREM.PRE_DAM END     
  FROM   T_ASSET_D_TEMP
  WHERE  ADPREM.CO_CD = T_ASSET_D_TEMP.CO_CD  AND ADPREM.ASSET_CD = T_ASSET_D_TEMP.ASSET_CD 
  AND ADPREM.GISU = @SCO_GISU  AND T_ASSET_D_TEMP.GROW_DAM > 0 --------
  */
  --处理折旧增加的数据,修改后上面的问题不会出现  SELECT * FROM T_ASSET_D_TEMP 
  
  DECLARE @LLLOOP INT,@LLGROW_DAM NUMERIC(17,4),@LLYM VARCHAR(6)
  SELECT @LLLOOP = 1
  IF (SELECT COUNT(*) FROM T_ASSET_D_TEMP WHERE GROW_DAM <> 0) > 0
    BEGIN
      WHILE @LLLOOP <= 12
        BEGIN
          SELECT @LLYM = LEFT(@FR_DT,4) + LEFT('00',2-LEN(CAST(@LLLOOP AS VARCHAR(2)))) + CAST(@LLLOOP AS VARCHAR(2)),
                 @LLGROW_DAM = 0
          SELECT @LLGROW_DAM = SUM(GROW_DAM)
          FROM   T_ASSET_D_TEMP
          WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND  CHG_YM = @LLYM
          UPDATE ADPREM
          SET    PRE_DAM = PRE_DAM + ISNULL(@LLGROW_DAM,0)
          WHERE  CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND DPRE_YM > @LLYM AND GISU = @SCO_GISU
          SET @LLLOOP = @LLLOOP + 1
        END
    END
  
  --
  
  -----------
  --取以前年度的变动金额(原值) 含12月 和 当年的原值变化
  DECLARE @LGROW_AM NUMERIC(17,4)
  DECLARE @LDROP_AM NUMERIC(17,4)
  DECLARE @CGROW_AM NUMERIC(17,4)
  DECLARE @CDROP_AM NUMERIC(17,4)
  
  SELECT @LGROW_AM = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0) ELSE 0 END),0),   
         @LDROP_AM = ISNULL(SUM(CASE CHG_FG WHEN '1' THEN ISNULL(CHG_AM,0)
                                            WHEN '2' THEN ISNULL(CHG_AM,0) ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND SUBSTRING(CHG_DT,1,6) <= CAST( (CAST(SUBSTRING(@FR_DT,1,4) AS INT) - 1 ) AS VARCHAR(4) ) + '12' 
  
  SELECT @CGROW_AM = ISNULL(SUM(CASE CHG_FG WHEN '0' THEN ISNULL(CHG_AM,0) ELSE 0 END),0)            
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND CHG_DT BETWEEN @FR_DT AND @TO_DT

  SELECT  @CDROP_AM = ISNULL(SUM(CASE CHG_FG WHEN '1' THEN ISNULL(CHG_AM,0)
                                             WHEN '2' THEN ISNULL(CHG_AM,0) ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND CHG_DT BETWEEN @FR_DT AND @TO_DT

  --取以前年度的变动金额(折旧) 含12月 和 当年的折旧变化
  DECLARE @LGROW_DAM NUMERIC(17,4)
  DECLARE @LDROP_DAM NUMERIC(17,4)
  DECLARE @CGROW_DAM NUMERIC(17,4)
  DECLARE @CDROP_DAM NUMERIC(17,4)

  SELECT @LGROW_DAM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN ISNULL(CHG_AM,0) ELSE 0 END),0),    
         @LDROP_DAM = ISNULL(SUM(CASE CHG_FG WHEN '6' THEN ISNULL(CHG_AM,0)   
                                             WHEN '2' THEN ISNULL(CHG_DAM,0) ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND SUBSTRING(CHG_DT,1,6) <= CAST( (CAST(SUBSTRING(@FR_DT,1,4) AS INT) - 1 ) AS VARCHAR(4) ) + '12' 

  SELECT @CGROW_DAM = ISNULL(SUM(CASE CHG_FG WHEN '5' THEN ISNULL(CHG_AM,0) ELSE 0 END),0),    
         @CDROP_DAM = ISNULL(SUM(CASE CHG_FG WHEN '6' THEN ISNULL(CHG_AM,0)   
                                             WHEN '2' THEN ISNULL(CHG_DAM,0) ELSE 0 END),0)
  FROM   ASSET_D
  WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
  AND CHG_DT BETWEEN @FR_DT AND @TO_DT
  --IF @FLAG1 = '0'  --未变卖  
  UPDATE ADPREY    
     SET CUR_DAM = (SELECT SUM(ISNULL(CUR_DAM,0)) FROM ADPREM WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND GISU = @SCO_GISU ) ,
         DROP_DAM = @CDROP_DAM,
         -- GROW_DAM = @CGROW_DAM,   
         GROW_AM = @CGROW_AM,
         DROP_AM = ISNULL(@CDROP_AM,0),    
         DPRE_MM = @ACCTERM, --CASE WHEN @ACCTERM > 12 THEN 12 ELSE @ACCTERM END,    
         PRE_AM  = (SELECT GET_AM FROM ASSET WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD) + ISNULL(@LGROW_AM,0) - ISNULL(@LDROP_AM,0) ,     
         SPEC_DAM = @ASSET_GETAM      
   WHERE CO_CD = @CO_CD     
     AND GISU  = @SCO_GISU     
     AND ASSET_CD = @ASSET_CD
   
   SELECT @RTN_AM = (SELECT SUM(ISNULL(CUR_DAM,0)) FROM ADPREM WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND GISU = @SCO_GISU )
  --变卖资产的处理-------------------------------------------------------------------------
  ZERO:  
  DECLARE CURSOR_DPREM CURSOR FOR  
    SELECT CO_CD,ASSET_CD,SEQ_NB,CHG_DT  
    FROM   ASSET_D  
    WHERE  CO_CD = @CO_CD AND CHG_FG = '2' AND SUBSTRING(CHG_DT,1,6) < = LEFT(@TO_DT,6) AND ASSET_CD = @ASSET_CD
    AND SUBSTRING(CHG_DT,1,4) = LEFT(@FR_DT,4)  

 

  DECLARE @LCO_CD VARCHAR(4)  
  DECLARE @LASSET_CD VARCHAR(10)  
  DECLARE @LSEQ_NB NUMERIC(5,0)  
  DECLARE @LLCHG_DT VARCHAR(8)  
  DECLARE @LPRE_AM NUMERIC(17,4)  
  DECLARE @LPRE_DAM NUMERIC(17,4)  
  DECLARE @LCUR_DAM NUMERIC(17,4)    
  DECLARE @LCHG_DAM NUMERIC(17,4) --处理以前的资产不减少,第一次结转是数据错误的问题
  
  
  OPEN CURSOR_DPREM  
  FETCH NEXT FROM CURSOR_DPREM INTO @LCO_CD,@LASSET_CD,@LSEQ_NB,@LLCHG_DT  
  WHILE @@FETCH_STATUS = 0  
   BEGIN  
     DELETE FROM ADPREM WHERE CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND GISU = @SCO_GISU AND DPRE_YM > LEFT(@LLCHG_DT,6)
     SET @LPRE_AM = 0  
     SELECT @LPRE_AM = SUM(GET_AM)  
     FROM   
      (SELECT ASSET_CD ,GET_AM  FROM   ASSET    
       WHERE  CO_CD = @LCO_CD AND SUBSTRING(GET_DT,1,6) < SUBSTRING(@LLCHG_DT,1,6) AND ASSET_CD = @LASSET_CD  
       UNION    
       SELECT ASSET_CD,(CASE  WHEN CHG_FG = '0' THEN CHG_AM  WHEN CHG_FG = '1' THEN  (-1* CHG_AM) ELSE 0 END ) AS CHG_AM   
       FROM ASSET_D   
       WHERE CO_CD = @LCO_CD AND SUBSTRING(CHG_DT,1,6) <= SUBSTRING(@LLCHG_DT,1,6) AND ASSET_CD = @LASSET_CD ) AS A1  
   
     SET @LPRE_AM = ISNULL(@LPRE_AM,0)  
       
     SELECT @LPRE_DAM = 0,@LCUR_DAM = 0  
     
     SELECT @LPRE_DAM = PRE_DAM  
     FROM   ADPREY  
     WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD   
     AND GISU = (SELECT GISU + (CAST(SUBSTRING(@LLCHG_DT,1,4) AS INT)-(CAST(SUBSTRING(FR_DT,1,4) AS INT)))FROM SCO WHERE CO_CD = @LCO_CD)   
     
     
     --资产变卖当月不能做折旧变动 DPRE_YM <= SUBSTRING(@LLCHG_DT,1,6) 修改成 DPRE_YM < SUBSTRING(@LLCHG_DT,1,6)
     SELECT @LCUR_DAM = SUM(ISNULL(GROW_DAM,0)) -  SUM(ISNULL(DROP_DAM,0)) 
     FROM   ADPREM 
     WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND DPRE_YM <= SUBSTRING(@LLCHG_DT,1,6) 
     AND LEFT(DPRE_YM,4) = LEFT(@LLCHG_DT,4)
     --SELECT * FROM ASSET_D
     SELECT  @LCHG_DAM = 0
     SELECT  @LCHG_DAM = CHG_DAM
     FROM    ASSET_D
     WHERE   CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND CHG_DT = @LLCHG_DT AND CHG_FG = '2'
     
     SELECT @LCUR_DAM = ISNULL(@LCUR_DAM,0) +  ISNULL(@LCHG_DAM,0)
     
     --PRINT @LCUR_DAM
            
     SELECT @LCUR_DAM = ISNULL(@LCUR_DAM,0) + ISNULL(PRE_DAM,0) + ISNULL(CUR_DAM,0)  ------  
     FROM   ADPREM   
     WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND DPRE_YM = (SELECT MAX(DPRE_YM) FROM ADPREM 
                                                                         WHERE DPRE_YM <= SUBSTRING(@LLCHG_DT,1,6) AND ASSET_CD = @ASSET_CD 
                                                                         AND LEFT(DPRE_YM,4) = LEFT(@LLCHG_DT,4) AND (PRE_DAM <> 0 OR CUR_DAM <> 0) ) 
     --PRINT @LCUR_DAM
     
     
     UPDATE ASSET_D   
     SET    CHG_AM = @LPRE_AM,CHG_DAM = ISNULL(@LPRE_DAM,0) + ISNULL(@LCUR_DAM,0),CHG_DOC = N'资产变卖/报废'  
     WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND SEQ_NB = @LSEQ_NB

     IF EXISTS (SELECT 1 FROM ADPREM WHERE CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND DPRE_YM = LEFT(@LLCHG_DT,6) )
        UPDATE ADPREM
        SET    DROP_DAM = ISNULL(@LPRE_DAM,0) + ISNULL(@LCUR_DAM,0),DROP_AM = @LPRE_AM
        WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND DPRE_YM = LEFT(@LLCHG_DT,6)
     ELSE
        INSERT INTO ADPREM (CO_CD,GISU,ASSET_CD,DPRE_YM,DROP_DAM,DROP_AM)
                    VALUES (@LCO_CD,@SCO_GISU,@LASSET_CD,LEFT(@LLCHG_DT,6),ISNULL(@LPRE_DAM,0) + ISNULL(@LCUR_DAM,0),@LPRE_AM)

     SELECT @CDROP_AM = 0,@CDROP_DAM = 0

     SELECT @CDROP_AM = ISNULL(SUM(CASE CHG_FG WHEN '1' THEN ISNULL(CHG_AM,0)
                                                WHEN '2' THEN ISNULL(CHG_AM,0) ELSE 0 END),0)
     FROM   ASSET_D
     WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
     AND CHG_DT BETWEEN @FR_DT AND @TO_DT

     SELECT @CDROP_DAM = ISNULL(SUM(CASE CHG_FG WHEN '6' THEN ISNULL(CHG_AM,0)   
                                             WHEN '2' THEN ISNULL(CHG_DAM,0) ELSE 0 END),0)
     FROM   ASSET_D
     WHERE  ASSET_CD = @ASSET_CD AND CO_CD = @CO_CD 
     AND CHG_DT BETWEEN @FR_DT AND @TO_DT

       

     UPDATE ADPREY
     SET    DROP_AM = @CDROP_AM,DROP_DAM = @CDROP_DAM,
            CUR_DAM = (SELECT SUM(ISNULL(CUR_DAM,0)) FROM ADPREM WHERE CO_CD = @CO_CD AND ASSET_CD = @ASSET_CD AND GISU = @SCO_GISU )
     WHERE  CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND GISU = @SCO_GISU
     
     --DELETE FROM ADPREM WHERE CO_CD = @LCO_CD AND ASSET_CD = @LASSET_CD AND GISU = @SCO_GISU AND DPRE_YM > LEFT(@LLCHG_DT,6)
     
     FETCH NEXT FROM CURSOR_DPREM INTO @LCO_CD,@LASSET_CD,@LSEQ_NB,@LLCHG_DT  
   END    
   CLOSE CURSOR_DPREM  
   DEALLOCATE CURSOR_DPREM  
  
  --------------------------------------------------     
  IF( @@ERROR <> 0 )    
  BEGIN    
       RAISERROR('ERROR OCCURED ON RECREATION YEARLY DEPRECIATION DATA .[7]',16,1)    
       ROLLBACK TRANSACTION     
       SET ANSI_WARNINGS ON    
       RETURN    
  END    
    
     -- IN CASE DOMPLETION OF DEPRECIATION, COMPLETION MASTER DEPRECIATION    
  IF (@MRAT = 0.0)    
  BEGIN    
       
       SET @DPRE_ST = '2'   
       UPDATE ASSET    
          SET DPRE_ST  = '2'    
        WHERE CO_CD    = @CO_CD    
          AND ASSET_CD = @ASSET_CD    
       IF( @@ERROR <> 0 )    
       BEGIN    
          RAISERROR('ERROR OCCURED ON MODIFICATION ASSET MASTER[8]',16,1)    
          ROLLBACK TRANSACTION     
          SET ANSI_WARNINGS ON    
          RETURN    
      END    
  END    
  ELSE    
  BEGIN    
          SET @DPRE_ST = '1' 
          UPDATE ASSET    
             SET DPRE_ST  = '1'    
           WHERE CO_CD    = @CO_CD    
             AND ASSET_CD = @ASSET_CD    
         IF( @@ERROR <> 0 )    
         BEGIN    
           RAISERROR('ERROR OCCURED ON MODIFICATION ASSET MASTER.[8]',16,1)    
           ROLLBACK TRANSACTION     
           SET ANSI_WARNINGS ON    
           RETURN    
         END    
    
  END    
    
 
  --COMMIT TRANSACTION     
    
  IF( @@ERROR <> 0 )    
  BEGIN    
     RAISERROR('CANNOT CREATE DEPRECIATION DATA.[TRANSACTION ERROR][9]',16,1)    
     ROLLBACK TRANSACTION     
     SET ANSI_WARNINGS ON    
     RETURN    
  END    
  
SET NOCOUNT OFF    
SET ANSI_WARNINGS ON    
  
END      --(SELECT CON_CD FROM SDBCFG) = 'CHS'  
  
-----------------------------------------------------------------------------------------------------------------------------------  

SET NOCOUNT OFF      
SET ANSI_WARNINGS ON      
    
END