DELETE FROM SMENU WHERE MENU_CD = 'ACA1990'
DELETE FROM SMSG WHERE FILE_NM = 'ACA1990'
INSERT INTO SMENU (MENU_CD,MODULE_CD,EXE_NM,MENU_CHS_NM,MENU_NM,INS_YN,EDIT_YN,DEL_YN,PRT_YN,REG_DT,LEVEL_CD,TEMP_CD)
            VALUES('ACA1990','A','ACA1990','月末损益结转设置',N'월말손익이월설정',0,1,0,1,'20040101',1,0)
INSERT INTO SMSG (FILE_NM,ID_NO,KOR_NM,CHS_NM,DUMMY1,ENG_NM,JPN_NM)
            SELECT 'ACA1990',ID_NO,KOR_NM,CHS_NM,DUMMY1,ENG_NM,JPN_NM
            FROM    SMSG WHERE FILE_NM = 'SJS8000'

DELETE FROM SMENU WHERE MENU_CD = 'ACA2000'
DELETE FROM SMSG WHERE FILE_NM = 'ACA2000'
INSERT INTO SMENU (MENU_CD,MODULE_CD,EXE_NM,MENU_CHS_NM,MENU_NM,INS_YN,EDIT_YN,DEL_YN,PRT_YN,REG_DT,LEVEL_CD,TEMP_CD)
            VALUES('ACA2000','A','ACA2000','月末损益结转',N'월말손익이월',1,1,1,1,'20040101',1,0)
INSERT INTO SMSG (FILE_NM,ID_NO,KOR_NM,CHS_NM,DUMMY1,ENG_NM,JPN_NM)
            SELECT 'ACA2000',ID_NO,KOR_NM,CHS_NM,DUMMY1,ENG_NM,JPN_NM
            FROM    SMSG WHERE FILE_NM = 'SJS8000'
GO

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'T_CARRYF_MONTHLY' AND XTYPE = 'U') DROP TABLE T_CARRYF_MONTHLY
   CREATE TABLE T_CARRYF_MONTHLY (CO_CD  VARCHAR(4) NOT NULL,
                                  CFG_NO VARCHAR(4) NOT NULL,
                                  CFG_NM VARCHAR(20),
                                  CFG_NMK VARCHAR(20) COLLATE Korean_Wansung_CI_AS,
                                  CFG_VALUES VARCHAR(10),
                                  REMARKS VARCHAR(50),
                                  REMARKS_K VARCHAR(50) COLLATE Korean_Wansung_CI_AS,
                CONSTRAINT T_CARRYF_MONTHLY_PK PRIMARY KEY (CO_CD,CFG_NO)  )   
GO

INSERT INTO T_CARRYF_MONTHLY (CO_CD,CFG_NO,CFG_NM,CFG_NMK,CFG_VALUES,REMARKS,REMARKS_K) VALUES ('1000','0010','本年利润科目',N'','3131','直接输入本年利润科目编码',N'')
INSERT INTO T_CARRYF_MONTHLY (CO_CD,CFG_NO,CFG_NM,CFG_NMK,CFG_VALUES,REMARKS,REMARKS_K) VALUES ('1000','0020','结转凭证类型',N'','5','直接输入凭证类型编码',N'')
INSERT INTO T_CARRYF_MONTHLY (CO_CD,CFG_NO,CFG_NM,CFG_NMK,CFG_VALUES,REMARKS,REMARKS_K) VALUES ('1000','0030','损益科目级次',N'','3','1-一级科目,2-二级科目,3-三级科目',N'')
INSERT INTO T_CARRYF_MONTHLY (CO_CD,CFG_NO,CFG_NM,CFG_NMK,CFG_VALUES,REMARKS,REMARKS_K) VALUES ('1000','0040','凭证数据依据',N'','1','1-包含未记帐的数据,2-只结转已记帐的数据',N'')

GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DZGOLD].[SPA_A_CARRYF_MONTHLY]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [DZGOLD].[SPA_A_CARRYF_MONTHLY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
--EXEC ACCOUNT_CARRY_FORWARD_MONTHLY '2000','1000','200403','3131','0402001'
CREATE  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')  AND LEFT(@YM,4)/4*4 = LEFT(@YM,4)
        SET @ISU_DT = @YM + '29'     
     IF SUBSTRING(@YM,5,2) IN ('02')  AND LEFT(@YM,4)/4*4 <> LEFT(@YM,4)
        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.MM_SQ_ISU_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.MM_SQ_ISU_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

DELETE FROM SCUSTREPORT_EXE WHERE EXE_CD = 'ACA1990'
INSERT INTO SCUSTREPORT_EXE (EXE_CD,TITLE,TITLE_NMK,QUERY,REMARK_DC,ORIENT,LEFTMARGIN,TOPMARGIN,PRINT_RT,FILE_RDS,LISTCOUNT,LISTHEIGHT,QUERY_F)
VALUES('ACA1990','月末损益结转设置',N'월말손익이월설정',':SQL_BEGIN
 SELECT CFG_NO,CFG_NM,CFG_VALUES,REMARKS,CO_CD 
 FROM T_CARRYF_MONTHLY
 WHERE CO_CD = :[00]
 ORDER BY CFG_NO
:SQL_END

[TITLE]:编号,名称,参数值,备注;
[LENGTH]:8,20,8,50,0;

[MODIFY_TABLE]:T_CARRYF_MONTHLY;
[MODIFY_PK]:0,4;
[MODIFY_FIELD]:2;', '',2,10,20,100,'',0,0,
 N':SQL_BEGIN
 SELECT CFG_NO,CFG_NM,CFG_VALUES,REMARKS,CO_CD 
 FROM T_CARRYF_MONTHLY
 WHERE CO_CD = :[00]
 ORDER BY CFG_NO
:SQL_END

[TITLE]:编号,名称,参数值,备注;
[LENGTH]:8,20,8,50,0;

[MODIFY_TABLE]:T_CARRYF_MONTHLY;
[MODIFY_PK]:0,4;
[MODIFY_FIELD]:2;')

GO

DELETE FROM SCUSTREPORT_EXE WHERE EXE_CD = 'ACA2000'
INSERT INTO SCUSTREPORT_EXE (EXE_CD,TITLE,TITLE_NMK,QUERY,REMARK_DC,ORIENT,LEFTMARGIN,TOPMARGIN,PRINT_RT,FILE_RDS,LISTCOUNT,LISTHEIGHT,QUERY_F)
VALUES('ACA2000','月末损益结转',N'월말손익이월',':SQL_BEGIN 
 SELECT A.ISU_DT,A.CTD_NM,A.MM_SQ,A.ACCT_CD,A.PRT_NM,A.DRCR_FG,A.ACCT_AM 
 FROM  
  (SELECT B.ISU_DT,E.CTD_NM,D.MM_SQ,B.ACCT_CD,C.PRT_NM,B.RMK_DC,CAST(B.ACCT_AM AS NUMERIC(17,2)) ACCT_AM,B.LN_SQ, 
          (CASE WHEN B.DRCR_FG = ''3'' THEN ''借'' ELSE ''贷'' END) DRCR_FG 
   FROM ADOCUH A  
   INNER JOIN ADOCUD B ON A.CO_CD = B.CO_CD AND A.DIV_CD = B.DIV_CD  
         AND A.ISU_DT = B.ISU_DT AND A.ISU_SQ = B.ISU_SQ 
   INNER JOIN SACCT C ON B.CO_CD = C.CO_CD AND B.ACCT_CD = C.ACCT_CD  
   INNER JOIN ADOCUSEQ D ON A.ISU_DT = D.ISU_DT AND A.CO_CD = D.CO_CD AND A.ISU_SQ = D.ISU_SQ AND D.MM_SQ_ISU_MM = :[02]
   INNER JOIN SCTRL_D E ON D.CO_CD = E.CO_CD AND D.GET_FG = E.CTD_CD AND E.MODULE_CD = ''A'' AND E.CTRL_CD = ''21'' 
   WHERE A.INSERT_IP = :[02]+''99'' AND A.CO_CD = :[00] AND A.DIV_CD = :[01]) A 
 ORDER BY A.MM_SQ 
:SQL_END 
[01]:@T会计主体@H(DIV_CD); 
[02]:@T结转年月@H(YM); 
[05]:@T操作员@H(EMP_CD); 
  
[TITLE]:日期,凭证类型,凭证号,科目编码,科目名称,方向,金额; 
[LENGTH]:10,10,6,10,20,5,15; 
[DATEMASK]:0; 
[PROCEDURE]:SPA_A_CARRYF_MONTHLY (:[00],:[01],:[02],:[05]);', '',2,10,20,100,'',0,0,
 N':SQL_BEGIN 
 SELECT A.ISU_DT,A.CTD_NM,A.MM_SQ,A.ACCT_CD,A.PRT_NM,A.DRCR_FG,A.ACCT_AM 
 FROM  
  (SELECT B.ISU_DT,E.CTD_NM,D.MM_SQ,B.ACCT_CD,C.PRT_NM,B.RMK_DC,CAST(B.ACCT_AM AS NUMERIC(17,2)) ACCT_AM,B.LN_SQ, 
          (CASE WHEN B.DRCR_FG = ''3'' THEN ''借'' ELSE ''贷'' END) DRCR_FG 
   FROM ADOCUH A  
   INNER JOIN ADOCUD B ON A.CO_CD = B.CO_CD AND A.DIV_CD = B.DIV_CD  
         AND A.ISU_DT = B.ISU_DT AND A.ISU_SQ = B.ISU_SQ 
   INNER JOIN SACCT C ON B.CO_CD = C.CO_CD AND B.ACCT_CD = C.ACCT_CD  
   INNER JOIN ADOCUSEQ D ON A.ISU_DT = D.ISU_DT AND A.CO_CD = D.CO_CD AND A.ISU_SQ = D.ISU_SQ AND D.MM_SQ_ISU_MM = :[02]
   INNER JOIN SCTRL_D E ON D.CO_CD = E.CO_CD AND D.GET_FG = E.CTD_CD AND E.MODULE_CD = ''A'' AND E.CTRL_CD = ''21'' 
   WHERE A.INSERT_IP = :[02]+''99'' AND A.CO_CD = :[00] AND A.DIV_CD = :[01]) A 
 ORDER BY A.MM_SQ 
:SQL_END 
[01]:@T会计主体@H(DIV_CD); 
[02]:@T结转年月@H(YM); 
[05]:@T操作员@H(EMP_CD); 
  
[TITLE]:日期,凭证类型,凭证号,科目编码,科目名称,方向,金额; 
[LENGTH]:10,10,6,10,20,5,15; 
[DATEMASK]:0; 
[PROCEDURE]:SPA_A_CARRYF_MONTHLY (:[00],:[01],:[02],:[05]);')

GO