SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--SELECT * FROM T_MARGIN_REPORT ORDER BY RNK_NB

ALTER   PROC QUERY_MARGIN_REPORT_NOTCHARGE @CO_CD VARCHAR(4),@DIV_CD VARCHAR(4),@FROM_DT VARCHAR(8),@TO_DT VARCHAR(8)
AS
BEGIN
  IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'T_MARGIN_REPORT' AND XTYPE = 'U')
     DROP TABLE T_MARGIN_REPORT

  DECLARE @FF VARCHAR(8)
  SET @FF = SUBSTRING(@FROM_DT,1,4) + '0101'
  SELECT A.RNK_NB,A.FS_CD, A.FS_NM FS_NM, A.ROW_ID, ROUND( DD.BD_AM, 2, 0 ) AS BD_AM, ROUND( DD.TD_AM, 2, 0 ) AS TD_AM 
  INTO T_MARGIN_REPORT
  FROM SFSCD A 
  LEFT OUTER JOIN (SELECT F.RNK_NB, CASE F.CALC_FG WHEN '4' THEN SUM(CASE WHEN M.DRCR_FG='2' THEN B_AM 
                                                                        WHEN M.DRCR_FG='1' THEN B_AM *-1 ELSE 0 END )*-1 
                                                 ELSE SUM(CASE WHEN M.DRCR_FG='2' THEN B_AM 
                                                               WHEN M.DRCR_FG='1' THEN B_AM *-1 ELSE 0 END ) END AS BD_AM, 
                                  CASE F.CALC_FG WHEN '4' THEN SUM(CASE WHEN M.DRCR_FG='2' THEN T_AM 
                                                                        WHEN M.DRCR_FG='1' THEN T_AM *-1 ELSE 0 END )*-1 
                                                 ELSE SUM(CASE WHEN M.DRCR_FG='2' THEN T_AM 
                                                               WHEN M.DRCR_FG='1' THEN T_AM *-1 ELSE 0 END ) END AS TD_AM 
                 FROM (SELECT D.CO_CD, D.ACCT_CD AS ACCT_CD,S.DRCR_FG, 
                              CASE WHEN D.ISU_DT>=@FROM_DT AND D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('1','3' ) AND S.DRCR_FG='1' 
                                        THEN D.ACCT_AM 
                                   WHEN D.ISU_DT>=@FROM_DT AND D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('2','4' ) AND S.DRCR_FG='2' 
                                        THEN D.ACCT_AM ELSE 0 END AS B_AM, 
                              CASE WHEN D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('1','3' ) AND S.DRCR_FG='1' THEN D.ACCT_AM 
                                   WHEN D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('2','4' ) AND S.DRCR_FG='2' THEN D.ACCT_AM 
                                   ELSE 0 END AS T_AM 
                       FROM ADOCUD D LEFT OUTER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD 
                       WHERE D.CO_CD=@CO_CD AND D.ISU_DT BETWEEN @FF AND @TO_DT AND D.DIV_CD IN (@DIV_CD) 
                             AND D.ACCT_CD>'40000000') M 
                 LEFT OUTER JOIN SFSCD F ON M.ACCT_CD BETWEEN F.FACCT_CD AND F.TACCT_CD AND F.FS_FG='3' AND F.TP_FG='0' AND F.CALC_FG<>'5'
                 GROUP BY F.CALC_FG,F.RNK_NB 
                 UNION ALL SELECT L.RNK_NB, SUM(CASE WHEN L.CALC_FG='4' THEN K.BD_AM *-1 ELSE K.BD_AM END) AS BD_AM, 
                                            SUM(CASE WHEN L.CALC_FG='4' THEN K.TD_AM *-1 ELSE K.TD_AM END) AS TD_AM 
                           FROM ( SELECT F.FS_CD, SUM(CASE WHEN M.DRCR_FG='2' THEN B_AM 
                                                           WHEN M.DRCR_FG='1' THEN B_AM *-1 ELSE 0 END ) BD_AM, 
                                         SUM(CASE WHEN M.DRCR_FG='2' THEN T_AM WHEN M.DRCR_FG='1' THEN T_AM *-1 ELSE 0 END ) TD_AM 
                                  FROM ( SELECT D.CO_CD, D.ACCT_CD AS ACCT_CD,S.DRCR_FG, 
                                        CASE WHEN D.ISU_DT>=@FROM_DT AND D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('1','3' ) AND S.DRCR_FG='1' 
                                             THEN D.ACCT_AM 
                                        WHEN D.ISU_DT>=@FROM_DT AND D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('2','4' ) AND S.DRCR_FG='2' 
                                            THEN D.ACCT_AM ELSE 0 END AS B_AM, 
                                       CASE WHEN D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('1','3' ) AND S.DRCR_FG='1' THEN D.ACCT_AM 
                                         WHEN D.ISU_DT <= @TO_DT AND D.DRCR_FG IN ('2','4' ) AND S.DRCR_FG='2' THEN D.ACCT_AM 
                                           ELSE 0 END AS T_AM 
                                       FROM ADOCUD D LEFT OUTER JOIN SACCT S ON D.CO_CD=S.CO_CD AND D.ACCT_CD=S.ACCT_CD 
                                       WHERE D.CO_CD=@CO_CD AND D.ISU_DT BETWEEN @FF AND @TO_DT AND D.DIV_CD IN (@DIV_CD) 
                                        AND D.ACCT_CD>'40000000') M 
                                 LEFT OUTER JOIN SFSCD F ON M.ACCT_CD BETWEEN F.FACCT_CD AND F.TACCT_CD AND F.FS_FG='3' AND F.TP_FG='0'AND F.CALC_FG<>'5'
                                 GROUP BY F.CALC_FG,F.FS_CD ) K 
                          INNER JOIN SFSCD L ON K.FS_CD BETWEEN L.FACCT_CD AND L.TACCT_CD AND L.FS_FG='3' AND L.TP_FG='0' 
                                                AND L.CALC_FG='5' 
                          GROUP BY L.CALC_FG,L.RNK_NB) DD 
  ON A.RNK_NB=DD.RNK_NB 
  WHERE A.FS_FG='3' AND A.TP_FG='0' 

DECLARE @LACCT_AM NUMERIC(17,4) 
DECLARE @LACCT_RAM NUMERIC(17,4) 

SELECT @LACCT_AM = SUM(ISNULL(ACCT_AM,0))
FROM   ADOCUD
WHERE  SUBSTRING(ACCT_CD,1,4) = '5405' AND ISU_DT BETWEEN @FROM_DT AND @TO_DT AND DRCR_FG = '3' 
AND    CO_CD = @CO_CD AND DIV_CD = @DIV_CD
SELECT @LACCT_RAM = SUM(ISNULL(ACCT_AM,0))
FROM   ADOCUD
WHERE  SUBSTRING(ACCT_CD,1,4) = '5405' AND ISU_DT BETWEEN @FF AND @TO_DT AND DRCR_FG = '3'
AND    CO_CD = @CO_CD AND DIV_CD = @DIV_CD

UPDATE T_MARGIN_REPORT
SET BD_AM = ISNULL(BD_AM,0) - ISNULL(@LACCT_AM,0),TD_AM = ISNULL(TD_AM,0) - ISNULL(@LACCT_RAM,0)
WHERE FS_CD IN ('6020','7010')


END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO