SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--EXEC CALCULATE_EXCHANGE_BY_MT '2000','1000','200403','USD',9,'550301','550302','0402001'

ALTER   PROC SPA_A_CARRYFF_MONTHLY @CO_CD VARCHAR(5),@DIV_CD VARCHAR(5),@YM VARCHAR(6),@MT VARCHAR(5),
                                   @RATE NUMERIC(17,6),@EMP_CD VARCHAR(10) 
AS
BEGIN
 IF @CO_CD = '' OR @DIV_CD = '' RETURN
 --@EXR_CD 收益科目,@EXP_CD 损失科目
 DECLARE @ISU_DT VARCHAR(8)
 DECLARE @EXR_CD VARCHAR(8)
 DECLARE @EXP_CD VARCHAR(8) 
 DECLARE @GET_FG  VARCHAR(4)
 DECLARE @VOU_FG  VARCHAR(4)
 DECLARE @TR_CD VARCHAR(5)

 SELECT @EXR_CD = CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0005'
 SELECT @EXP_CD = CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0010' 
 SELECT @GET_FG = CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CO_CD = @CO_CD AND CFG_NO = '0020'
 SELECT @VOU_FG = CFG_VALUES FROM T_CARRYFF_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_CAL_EX_MT' AND XTYPE = 'U')    DROP TABLE T_CAL_EX_MT

--临时表用来存放各个科目汇兑损益情况
SELECT C.ACCT_CD,C.TR_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.TR_CD,B.DRCR_FG,SUM(ISNULL(B.CT_AM,0)) CT_AM,SUM(ISNULL(B.ACCT_AM,0)) ACCT_AM,
       ROUND(SUM(ISNULL(B.CT_AM,0))*@RATE,2) LAST_AM,   
       ROUND(SUM(ISNULL(B.CT_AM,0))*@RATE,2) - SUM(ISNULL(B.ACCT_AM,0)) DIFF_AM 
FROM(
SELECT A.ACCT_CD,A.TR_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.TR_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 IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')
                   OR LEFT(ACCT_CD,4) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')      
                   OR LEFT(ACCT_CD,6) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')) 
              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 IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')
                   OR LEFT(ACCT_CD,4) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')      
                   OR LEFT(ACCT_CD,6) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010'))  )A )B
 ON A.ACCT_CD = B.ACCT_CD AND A.CT_DEAL = @MT
 WHERE  A.CO_CD = @CO_CD AND A.DIV_CD = @DIV_CD AND SUBSTRING(A.FILL_DT,1,4) =SUBSTRING(@YM,1,4)
 UNION ALL
 SELECT A.ACCT_CD,A.TR_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 IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')
                   OR LEFT(ACCT_CD,4) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')      
                   OR LEFT(ACCT_CD,6) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')) 
              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 IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')
                   OR LEFT(ACCT_CD,4) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010')      
                   OR LEFT(ACCT_CD,6) IN  (SELECT CFG_VALUES FROM T_CARRYFF_MONTHLY WHERE CFG_NO >= '2010'))  )A )B
 ON A.ACCT_CD = B.ACCT_CD AND A.CT_DEAL = @MT
 INNER JOIN ADOCUSEQ E ON A.CO_CD = E.CO_CD AND A.ISU_DT = E.ISU_DT AND A.ISU_SQ = E.ISU_SQ 
                       AND E.MM_SQ_ISU_MM BETWEEN (CASE WHEN @VOU_FG = '1' THEN LEFT(@YM,4) + '01' ELSE '000000' END)
                                            AND (CASE WHEN @VOU_FG = '1' THEN @YM  ELSE 'ZZZZZZ' END)
                       AND E.MM_SQ_FILL_MM BETWEEN (CASE WHEN @VOU_FG = '2' THEN LEFT(@YM,4) + '01' 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 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.TR_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',@GET_FG,@EMP_CD,@GET_FG,'00000000',0,@EMP_CD,GETDATE(),@YM+'98')    
 --处理凭证分录(外币部分)
 WHILE @LN_SQ < = @COUNT
  BEGIN
   SELECT @LACCT_CD = ACCT_CD,@TR_CD = TR_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,TR_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,@TR_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
  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