SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--EXEC ACCOUNT_CARRY_FORWARD_MONTHLY '2000','1000','200403','3131','0402001'
ALTER   PROC SPA_A_CARRYF_MONTHLY @CO_CD VARCHAR(5),@DIV_CD VARCHAR(5),@YM VARCHAR(6),@EMP_CD VARCHAR(10)
AS
--@ACCT_CD 本年利润科目
BEGIN
  IF @CO_CD = '' OR @DIV_CD = '' RETURN
  
  IF @YM < (SELECT LEFT(CLOSE_DT,6) FROM SCLOSE WHERE CO_CD = @CO_CD AND CLOSE_CD = '01' AND DIV_CD = @DIV_CD)
     RETURN  

  DECLARE @ISU_DT VARCHAR(8)
  DECLARE @ACCT_CD VARCHAR(8)
  DECLARE @GET_FG  VARCHAR(4)

  DECLARE @ACCT_FG VARCHAR(4)
  DECLARE @VOU_FG  VARCHAR(4)
  --取设置的参数
  SELECT @ACCT_CD = CFG_VALUES FROM T_CARRYF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0010'
  SELECT @GET_FG = CFG_VALUES FROM T_CARRYF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0020'
  SELECT @ACCT_FG = CFG_VALUES FROM T_CARRYF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0030'
  SELECT @VOU_FG = CFG_VALUES FROM T_CARRYF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0040'
  --设置凭证日期  
  IF (SELECT FG_TY FROM SYSCFG WHERE CO_CD = @CO_CD AND CTR_CD = '37' AND MODULE_CD = 'A' ) = '00'
    BEGIN
     IF SUBSTRING(@YM,5,2) IN ('01','03','05','07','08','10','12')  
        SET @ISU_DT = @YM + '31'
     IF SUBSTRING(@YM,5,2) IN ('04','06','09','11')  
        SET @ISU_DT = @YM + '30'
     IF SUBSTRING(@YM,5,2) IN ('02')  
        SET @ISU_DT = @YM + '28'     
    END     
  ELSE
    BEGIN 
     SET @ISU_DT = LEFT(@YM,6) + (SELECT FG_TY FROM SYSCFG WHERE CO_CD = @CO_CD AND CTR_CD = '37' AND MODULE_CD = 'A' )     
    END
     
  IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'T_CARRY_FORWARD' AND XTYPE = 'U') DROP TABLE T_CARRY_FORWARD
     CREATE TABLE T_CARRY_FORWARD
                  (ACCT_CD VARCHAR(8),
                   DRCR_FG VARCHAR(1),
                   ACCT_AM NUMERIC(17,4),
                   IDNO INT IDENTITY(1,1))

  INSERT INTO  T_CARRY_FORWARD (ACCT_CD,DRCR_FG,ACCT_AM)
  SELECT B.ACCT_CD,B.DRCR_FG,B.ACCT_AM
  FROM
  (SELECT A.ACCT_CD,(CASE WHEN A.DRCR_FG = '3' THEN '4' WHEN A.DRCR_FG = '4' THEN '3' END) DRCR_FG,A.ACCT_AM
   FROM
    (SELECT C.ACCT_CD,(CASE WHEN C.DRCR_FG = '1' THEN '3' ELSE '4' END) DRCR_FG,SUM(C.ACCT_AM) ACCT_AM
     FROM
      (SELECT LEFT(A.ACCT_CD,CASE WHEN @ACCT_FG = '1' THEN 4 WHEN @ACCT_FG = '2' THEN 6 ELSE 8 END) ACCT_CD,B.DRCR_FG,
             (CASE WHEN ((A.DRCR_FG = '3' AND B.DRCR_FG = '1') OR (A.DRCR_FG = '4' AND B.DRCR_FG = '2')) THEN A.ACCT_AM 
                   ELSE -A.ACCT_AM END) ACCT_AM
       FROM ADOCUD A
       INNER JOIN SACCT B ON A.CO_CD = B.CO_CD 
                         AND LEFT(A.ACCT_CD,CASE WHEN @ACCT_FG = '1' THEN 4 WHEN @ACCT_FG = '2' THEN 6 ELSE 8 END) = B.ACCT_CD
       INNER JOIN ADOCUSEQ C ON A.CO_CD = C.CO_CD AND A.ISU_DT = C.ISU_DT AND A.ISU_SQ = C.ISU_SQ
                             AND C.DOCU_MM BETWEEN (CASE WHEN @VOU_FG = '1' THEN @YM ELSE '000000' END)
                                           AND (CASE WHEN @VOU_FG = '1' THEN @YM ELSE 'ZZZZZZ' END)
                             AND C.MM_SQ_FILL_MM BETWEEN (CASE WHEN @VOU_FG = '2' THEN @YM ELSE '000000' END)
                                           AND (CASE WHEN @VOU_FG = '2' THEN @YM ELSE 'ZZZZZZ' END)
       WHERE A.CO_CD = @CO_CD AND A.DIV_CD = @DIV_CD AND   A.ACCT_CD LIKE '5%' )C
     GROUP BY C.ACCT_CD,C.DRCR_FG ) A
   UNION ALL
   SELECT D.ACCT_CD,(CASE WHEN D.ACCT_AM < 0 THEN '4' WHEN D.ACCT_AM > 0 THEN '3' ELSE '3' END) DRCR_FG,ABS(D.ACCT_AM) ACCT_AM
   FROM 
    (SELECT @ACCT_CD ACCT_CD,'3' DRCR_FG,SUM(CASE WHEN C.DRCR_FG = '3' THEN -1*C.ACCT_AM 
                                                WHEN C.DRCR_FG = '4' THEN C.ACCT_AM ELSE 0 END) ACCT_AM
     FROM
      (SELECT A.ACCT_CD,(CASE WHEN A.DRCR_FG = '1' THEN '4' WHEN A.DRCR_FG = '2' THEN '3' END) DRCR_FG,A.ACCT_AM
       FROM   
        (SELECT LEFT(A.ACCT_CD,CASE WHEN @ACCT_FG = '1' THEN 4 WHEN @ACCT_FG = '2' THEN 6 ELSE 8 END) ACCT_CD,B.DRCR_FG,
                (CASE WHEN ((A.DRCR_FG = '3' AND B.DRCR_FG = '1') OR (A.DRCR_FG = '4' AND B.DRCR_FG = '2')) THEN A.ACCT_AM 
                 ELSE -A.ACCT_AM END) ACCT_AM 
         FROM ADOCUD A
         INNER JOIN SACCT B ON A.CO_CD = B.CO_CD 
                            AND LEFT(A.ACCT_CD,CASE WHEN @ACCT_FG = '1' THEN 4 WHEN @ACCT_FG = '2' THEN 6 ELSE 8 END) = B.ACCT_CD
         INNER JOIN ADOCUSEQ C ON A.CO_CD = C.CO_CD AND A.ISU_DT = C.ISU_DT AND A.ISU_SQ = C.ISU_SQ
                             AND C.DOCU_MM BETWEEN (CASE WHEN @VOU_FG = '1' THEN @YM ELSE '000000' END)
                                           AND (CASE WHEN @VOU_FG = '1' THEN @YM ELSE 'ZZZZZZ' END)
                             AND C.MM_SQ_FILL_MM BETWEEN (CASE WHEN @VOU_FG = '2' THEN @YM ELSE '000000' END)
                                           AND (CASE WHEN @VOU_FG = '2' THEN @YM ELSE 'ZZZZZZ' END)
         WHERE A.CO_CD = @CO_CD AND A.DIV_CD = @DIV_CD AND A.ACCT_CD LIKE '5%' )A )C ) D )B         
  WHERE B.ACCT_AM <> 0
  ORDER BY B.DRCR_FG

  DECLARE @COUNT NUMERIC(5,0)
  DECLARE @LACCT_CD VARCHAR(10)  
  DECLARE @LDRCR_FG VARCHAR(1)
  DECLARE @LACCT_AM NUMERIC(17,4)
  DECLARE @ISU_SQ NUMERIC(5,0)
  DECLARE @DEPT_CD VARCHAR(4)
  DECLARE @LN_SQ NUMERIC(5,0)
  SET @LN_SQ = 1

  SELECT @COUNT = MAX(IDNO)
  FROM   T_CARRY_FORWARD
  SET @COUNT = ISNULL(@COUNT,0)
  
  SELECT @ISU_SQ = MAX(ISU_SQ) + 1
  FROM   ADOCUH 
  WHERE  CO_CD = @CO_CD AND ISU_DT = @ISU_DT  -- AND DIV_CD = @DIV_CD
  SELECT @DEPT_CD = DEPT_CD 
  FROM   SEMP 
  WHERE  CO_CD = @CO_CD AND EMP_CD = @EMP_CD
  
  IF @COUNT > 0 
     INSERT INTO ADOCUH (ISU_DT,ISU_SQ,CO_CD,DIV_CD,DEPT_CD,DOCU_ST,DOCU_TY,EMP_CD,GET_FG,FILL_DT,FILL_NB,INSERT_ID,INSERT_DT,INSERT_IP)  
               VALUES(@ISU_DT,ISNULL(@ISU_SQ,1),@CO_CD,@DIV_CD,@DEPT_CD,'0',@GET_FG,@EMP_CD,@GET_FG,'00000000',0,@EMP_CD,GETDATE(),@YM+'99')    
  
  WHILE @LN_SQ < = @COUNT
   BEGIN 
     SELECT @LACCT_CD = ACCT_CD,@LDRCR_FG = DRCR_FG,@LACCT_AM = ACCT_AM
     FROM   T_CARRY_FORWARD
     WHERE  IDNO = @LN_SQ

     INSERT INTO ADOCUD(ISU_DT,ISU_SQ,LN_SQ,DEPT_CD,CO_CD,DIV_CD,ACCT_CD,DRCR_FG,FILL_NB,FILL_DT,RMK_NB,RMK_DC,ACCT_AM)
         VALUES(@ISU_DT,ISNULL(@ISU_SQ,1),@LN_SQ,@DEPT_CD,@CO_CD,@DIV_CD,@LACCT_CD,@LDRCR_FG,0,'00000000',0,'月末损益结转',@LACCT_AM)      
     SET @LN_SQ = @LN_SQ + 1     
   END
  UPDATE ADOCUD
  SET    ADOCUD.TRCD_TY = SACCT.TRCD_TY,ADOCUD.TRNM_TY = SACCT.TRNM_TY,ADOCUD.DEPTCD_TY = SACCT.DEPTCD_TY,
         ADOCUD.PJTCD_TY = SACCT.PJTCD_TY,ADOCUD.CTNB_TY = SACCT.CTNB_TY,ADOCUD.FRDT_TY = SACCT.FRDT_TY,
         ADOCUD.TODT_TY = SACCT.TODT_TY,ADOCUD.QT_TY = SACCT.QT_TY,ADOCUD.AM_TY = SACCT.AM_TY,
         ADOCUD.RT_TY = SACCT.RT_TY,ADOCUD.DEAL_TY = SACCT.DEAL_TY,ADOCUD.USER1_TY = SACCT.USER1_TY,
         ADOCUD.USER2_TY = SACCT.USER2_TY 
  FROM   SACCT
  WHERE  ADOCUD.ACCT_CD = SACCT.ACCT_CD AND ADOCUD.CO_CD = SACCT.CO_CD
  AND    ADOCUD.CO_CD = @CO_CD AND ADOCUD.ISU_DT = @ISU_DT AND ADOCUD.ISU_SQ = ISNULL(@ISU_SQ,1)

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO