ALTER TABLE SCUSTREPORT_EXE ADD QUERY_F TEXT COLLATE Korean_Wansung_CI_AS NULL
GO
DELETE FROM SMENU WHERE MENU_CD = 'ACA2010'
DELETE FROM SMSG WHERE FILE_NM = 'ACA2010'
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('ACA2010','A','ACA2010','汇兑损益结转',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 'ACA2010',ID_NO,KOR_NM,CHS_NM,DUMMY1,ENG_NM,JPN_NM
            FROM    SMSG WHERE FILE_NM = 'SJS8000'
--汇兑损益结转
DELETE FROM SCUSTREPORT_EXE WHERE EXE_CD = 'ACA2010'
INSERT INTO SCUSTREPORT_EXE (EXE_CD,TITLE,TITLE_NMK,QUERY,REMARK_DC,ORIENT,LEFTMARGIN,TOPMARGIN,PRINT_RT,FILE_RDS,LISTCOUNT,LISTHEIGHT,QUERY_F)
VALUES('ACA2010','汇兑损益结转',N'외환차손이월',':SQL_BEGIN 
 SELECT A.ISU_DT,A.MM_SQ,A.ACCT_CD,A.PRT_NM,A.DRCR_FG,A.ACCT_AM 
 FROM  
  (SELECT B.ISU_DT,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  
   LEFT OUTER JOIN ADOCUSEQ D ON A.ISU_DT = D.ISU_DT AND A.CO_CD = D.CO_CD AND A.ISU_SQ = D.ISU_SQ 
   WHERE SUBSTRING(A.ISU_DT,1,6) = :[02] AND A.INSERT_IP = :[02]+''98'' ) A 
 ORDER BY A.LN_SQ 
 :SQL_END 
 [01]:@T会计主体@H(DIV_CD); 
 [02]:@T汇兑损益计算年月@H(YM); 
 [05]:@T币种@H(VACTD_CD_K2); 
 [06]:@T汇率@H(NUMERIC); 
 [07]:@T汇兑收益科目@H(ACCT_CD); 
 [08]:@T汇兑损失科目@H(ACCT_CD); 
 [09]:@T操作员@H(EMP_CD); 
  
 [TITLE]:日期,凭证号,科目编码,科目名称,方向,金额; 
 [LENGTH]:10,6,10,20,5,15; 
 [DATEMASK]:0; 
 [PROCEDURE]:CALCULATE_EXCHANGE_BY_MT (:[00],:[01],:[02],:[05],:[06],:[07],:[08],:[09]);', '',2,10,20,100,'',0,0,
 N':SQL_BEGIN 
 SELECT A.ISU_DT,A.MM_SQ,A.ACCT_CD,A.PRT_NMK,A.DRCR_FG,A.ACCT_AM 
 FROM  
  (SELECT B.ISU_DT,D.MM_SQ,B.ACCT_CD,C.PRT_NMK,B.RMK_DC,CAST(B.ACCT_AM AS NUMERIC(17,2)) ACCT_AM,B.LN_SQ, 
          (CASE WHEN B.DRCR_FG = ''3'' THEN ''De'' ELSE ''Cr'' 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  
   LEFT OUTER JOIN ADOCUSEQ D ON A.ISU_DT = D.ISU_DT AND A.CO_CD = D.CO_CD AND A.ISU_SQ = D.ISU_SQ 
   WHERE SUBSTRING(A.ISU_DT,1,6) = :[02] AND A.INSERT_IP = :[02]+''98'' ) A 
 ORDER BY A.LN_SQ 
 :SQL_END 
 [01]:@T회계단위@H(DIV_CD); 
 [02]:@T외환차손계산년월@H(YM); 
 [05]:@T환종@H(VACTD_CD_K2); 
 [06]:@T환율@H(NUMERIC); 
 [07]:@T외환수익계정@H(ACCT_CD); 
 [08]:@T외환차손계정@H(ACCT_CD); 
 [09]:@T작업자@H(EMP_CD); 
  
 [TITLE]:일자,전표번호,계정코드,계정명,방향,금액; 
 [LENGTH]:10,6,10,20,5,15; 
 [DATEMASK]:0; 
 [PROCEDURE]:CALCULATE_EXCHANGE_BY_MT (:[00],:[01],:[02],:[05],:[06],:[07],:[08],:[09]);')

--汇兑损益结转

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
--EXEC CALCULATE_EXCHANGE_BY_MT '2000','1000','200403','USD',9,'550301','550302','0402001'

CREATE  PROC CALCULATE_EXCHANGE_BY_MT @CO_CD VARCHAR(5),@DIV_CD VARCHAR(5),@YM VARCHAR(6),@MT VARCHAR(5),@RATE NUMERIC(17,6),
                                     @EXR_CD VARCHAR(10),@EXP_CD VARCHAR(10),@EMP_CD VARCHAR(10) 
AS
BEGIN
 DECLARE @ISU_DT VARCHAR(8)
  
  
  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'

 IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'T_CAL_EX_MT' AND XTYPE = 'U')    DROP TABLE T_CAL_EX_MT

--临时表用来存放各个科目汇兑损益情况
 SELECT C.ACCT_CD,(CASE WHEN C.DRCR_FG = '1' AND C.DIFF_AM > 0 THEN '3' WHEN C.DRCR_FG = '1' AND C.DIFF_AM < 0 THEN '4'
                      WHEN C.DRCR_FG = '2' AND C.DIFF_AM > 0 THEN '4' WHEN C.DRCR_FG = '2' AND C.DIFF_AM < 0 THEN '3' END) DRCR_FG,
       ABS(C.DIFF_AM) DIFF_AM,IDENTITY(INT,1,1) IDNO
 INTO T_CAL_EX_MT
FROM(
SELECT B.ACCT_CD,B.DRCR_FG,SUM(ISNULL(B.CT_AM,0)) CT_AM,SUM(ISNULL(B.ACCT_AM,0)) ACCT_AM,SUM(ISNULL(B.CT_AM,0))*@RATE LAST_AM, 
       SUM(ISNULL(B.CT_AM,0))*@RATE - SUM(ISNULL(B.ACCT_AM,0)) DIFF_AM
FROM(
SELECT A.ACCT_CD,B.DRCR_FG,A.DRCR_FG AS DRCR_FG1,
       (CASE WHEN A.DRCR_FG = '3' AND B.DRCR_FG = '1' THEN A.CT_AM WHEN A.DRCR_FG = '3' AND B.DRCR_FG = '2' THEN -A.CT_AM
            WHEN A.DRCR_FG = '4' AND B.DRCR_FG = '2' THEN A.CT_AM WHEN A.DRCR_FG = '4' AND B.DRCR_FG = '1' THEN -A.CT_AM END ) CT_AM, 
       (CASE WHEN A.DRCR_FG = '3' AND B.DRCR_FG = '1' THEN A.ACCT_AM WHEN A.DRCR_FG = '3' AND B.DRCR_FG = '2' THEN -A.ACCT_AM
            WHEN A.DRCR_FG = '4' AND B.DRCR_FG = '2' THEN A.ACCT_AM WHEN A.DRCR_FG = '4' AND B.DRCR_FG = '1' THEN -A.ACCT_AM END ) ACCT_AM                         
FROM
(SELECT A.ACCT_CD,A.DRCR_FG,A.CT_AM,A.ACCT_AM
 FROM   APREV A
 INNER JOIN (SELECT DISTINCT A.ACCT_CD
            FROM
             (SELECT DISTINCT ACCT_CD 
  	      FROM APREV 
              WHERE CO_CD = @CO_CD AND DIV_CD = @DIV_CD AND DEAL_TY = 'K2' AND CT_DEAL = @MT AND SUBSTRING(FILL_DT,1,4) =SUBSTRING(@YM,1,4)
              AND ACCT_CD BETWEEN '1001' AND '29999999'
              UNION ALL
              SELECT DISTINCT ACCT_CD 
              FROM ADOCUD 
              WHERE CO_CD = @CO_CD AND DIV_CD = @DIV_CD AND DEAL_TY = 'K2' AND CT_DEAL = @MT AND SUBSTRING(ISU_DT,1,4) =SUBSTRING(@YM,1,4)
              AND ACCT_CD BETWEEN '1001' AND '29999999')A )B
 ON A.ACCT_CD = B.ACCT_CD AND A.CT_DEAL = @MT
 WHERE  A.CO_CD = @CO_CD AND SUBSTRING(A.FILL_DT,1,4) =SUBSTRING(@YM,1,4)
 UNION ALL
 SELECT A.ACCT_CD,A.DRCR_FG,A.CT_AM,A.ACCT_AM
 FROM   ADOCUD A
 INNER JOIN (SELECT DISTINCT A.ACCT_CD
            FROM
             (SELECT DISTINCT ACCT_CD 
  	      FROM APREV 
              WHERE CO_CD = @CO_CD AND DIV_CD = @DIV_CD AND DEAL_TY = 'K2' AND CT_DEAL = @MT AND SUBSTRING(FILL_DT,1,4) =SUBSTRING(@YM,1,4)
              AND ACCT_CD BETWEEN '1001' AND '29999999'
              UNION ALL
              SELECT DISTINCT ACCT_CD 
              FROM ADOCUD 
              WHERE CO_CD = @CO_CD AND DIV_CD = @DIV_CD AND DEAL_TY = 'K2' AND CT_DEAL = @MT AND SUBSTRING(ISU_DT,1,4) =SUBSTRING(@YM,1,4)
              AND ACCT_CD BETWEEN '1001' AND '29999999')A )B
 ON A.ACCT_CD = B.ACCT_CD AND A.CT_DEAL = @MT
 WHERE  A.CO_CD = @CO_CD AND SUBSTRING(A.ISU_DT,1,4) =SUBSTRING(@YM,1,4) AND SUBSTRING(A.ISU_DT,1,6)<= @YM ) A
INNER JOIN SACCT B ON A.ACCT_CD = B.ACCT_CD AND B.CO_CD = @CO_CD ) B
GROUP BY B.ACCT_CD,B.DRCR_FG ) C 
WHERE C.DIFF_AM <> 0 
 
 DECLARE @COUNT INT
 DECLARE @LACCT_CD VARCHAR(10)
 DECLARE @LDRCR_FG VARCHAR(1)
 DECLARE @LACCT_AM NUMERIC(17,4)

 SELECT @COUNT = MAX(ISNULL(IDNO,0))
 FROM   T_CAL_EX_MT
 SET @COUNT = ISNULL(@COUNT,0)

 IF @COUNT = 0 RETURN --如果没有汇兑损益业务,退出存储过程

 DECLARE @ISU_SQ NUMERIC(5,0)
 DECLARE @DEPT_CD VARCHAR(4)
 DECLARE @LN_SQ NUMERIC(5,0)
 SET @LN_SQ = 1

 SELECT @ISU_SQ = MAX(ISU_SQ) + 1
 FROM   ADOCUH 
 WHERE  CO_CD = @CO_CD AND DIV_CD = @DIV_CD AND ISU_DT = @ISU_DT

 SELECT @DEPT_CD = DEPT_CD 
 FROM   SEMP 
 WHERE  CO_CD = @CO_CD AND EMP_CD = @EMP_CD

 SET @ISU_SQ = ISNULL(@ISU_SQ,1)
 --处理凭证表头
 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','1',@EMP_CD,'5','00000000',0,@EMP_CD,GETDATE(),@YM+'98')    
 --处理凭证分录(外币部分)
 WHILE @LN_SQ < = @COUNT
  BEGIN
   SELECT @LACCT_CD = ACCT_CD,@LDRCR_FG = DRCR_FG,@LACCT_AM = DIFF_AM
   FROM   T_CAL_EX_MT
   WHERE  IDNO = @LN_SQ
   
   INSERT INTO ADOCUD(ISU_DT,ISU_SQ,LN_SQ,DEPT_CD,CO_CD,DIV_CD,ACCT_CD,CT_DEAL,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,@MT,@LDRCR_FG,0,'00000000',0,'Carry Forward Exchange',@LACCT_AM)      
     
   SET @LN_SQ = @LN_SQ + 1
  END
  --处理凭证分录(汇兑损益科目部分) @EXR_CD 收益科目,@EXP_CD 损失科目
  SET @LACCT_AM = 0
  SELECT @LACCT_AM = SUM(DIFF_AM)
  FROM   T_CAL_EX_MT
  WHERE  DRCR_FG = '3'
  SET @LACCT_AM = ISNULL(@LACCT_AM,0)
  IF @LACCT_AM <> 0
     BEGIN
       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,@EXR_CD,'4',0,'00000000',0,'Carry Forward Exchange',@LACCT_AM)
       SET @LN_SQ = @LN_SQ + 1       
     END


  SET @LACCT_AM = 0
  SELECT @LACCT_AM = SUM(DIFF_AM)
  FROM   T_CAL_EX_MT
  WHERE  DRCR_FG = '4'
  SET @LACCT_AM = ISNULL(@LACCT_AM,0)
  IF @LACCT_AM <> 0
     BEGIN
       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,@EXP_CD,'3',0,'00000000',0,'Carry Forward Exchange',@LACCT_AM)         
     END
 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO