-- EXEC   
ALTER  PROC [dbo].[USP_ACBC031_BY_JECHUL_SELECT]    
(     
 @LANGKIND  NVARCHAR(3),  --언어종류( 필수, KOR, CHS, ENG, JPN 등)  
 @CO_CD   NVARCHAR(4),  -- 회사코드(필수)  
 @DIV_CD_STR     NTEXT,    -- 회계단위 ( 필수 )  
 @GISU   NUMERIC(3),   -- 기수(필수)  
 @FILL_DT_FROM NVARCHAR(8),  -- 시작일(필수)  
 @FILL_DT_TO  NVARCHAR(8),  -- 종료일(필수)  
  @FS_CD   NVARCHAR(3),    -- 기준 양식  
 @FS_TYPE  NVARCHAR(1)   -- '0' : 기장 기준 , '1' : 전체   
)  
--WITH ENCRYPTION  
AS  
/*************************************************************************/  
--설    명 : 회계관리 - 중국 대차대조표 -  제출용 조회  
--수 정 자 : 김대영  
--수정일자 : 2008/05/12  
--수정내역 : 2008/05/12 : 신규 
--MODIFY R1: 2010/05/12 : SHI LEI : 按标签LEVEL_CD顺序计算 2->0 
/***/  
BEGIN  
   
  
-- EXEC dbo.USP_ACBC031_BY_JECHUL_SELECT 'KOR','1000','1000|',1,'20080101','20081231','000','0'  
    
 DECLARE @LINE_NO  NVARCHAR(4),   
   @ACCT_AM  NUMERIC(20,4),  
   @PREV_AM  NUMERIC(20,4)    
   
 -- 임시 테이블 생성   
 CREATE TABLE #BALANCESHEET  
 (  
  FS_CD  NVARCHAR(3)  COLLATE database_default  NULL,  
  LINE_NO  NVARCHAR(4)  COLLATE database_default  NULL,  
  DISP_NO  NVARCHAR(4)  COLLATE database_default  NULL,  
  EXP_TY  NVARCHAR(1)  COLLATE database_default  NULL,  
  LEVEL_CD NVARCHAR(1)  COLLATE database_default  NULL,  
  DISP_NM  NVARCHAR(60) COLLATE database_default  NULL,  
  CALC_TY  NVARCHAR(1)  COLLATE database_default  NULL,  
  DISP_NMK NVARCHAR(60) COLLATE database_default  NULL,  
  ID_FG  NVARCHAR(1)  COLLATE database_default  NULL,  
  ACCT_CD  NVARCHAR(8)  COLLATE database_default  NULL,  
  ACCT_AM  NUMERIC(20,4),  
  PREV_AM  NUMERIC(20,4),     
  TYPE  NVARCHAR(3)  COLLATE database_default  NULL   
 )   
   
 -- 기장 기준   
 IF( @FS_TYPE = '0')  
 BEGIN  
  INSERT INTO #BALANCESHEET (FS_CD, LINE_NO, DISP_NO, EXP_TY, LEVEL_CD , DISP_NM, CALC_TY, DISP_NMK, ID_FG,  ACCT_CD, ACCT_AM , PREV_AM, TYPE )  
  SELECT FS_CD, LINE_NO, DISP_NO, EXP_TY, LEVEL_CD , DISP_NM, CALC_TY, DISP_NMK, ID_FG,  ACCT_CD, ACCT_AM , PREV_AM, TYPE   
  FROM   
  (  
   SELECT G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, H.ID_FG,    
       CASE WHEN ISNULL(G.EXP_TY,'0') ='0' THEN SUBSTRING(A.ACCT_CD ,1,4) ELSE SUBSTRING(A.ACCT_CD,1,CAST( G.EXP_TY AS INT )) END ACCT_CD  , 
       SUM(CASE WHEN A.DRCR_FG = T.DRCR_FG THEN  ISNULL(A.ACCT_AM,0) ELSE -1*ISNULL(A.ACCT_AM,0) END )  ACCT_AM , 
       SUM(CASE WHEN A.DRCR_FG = T.DRCR_FG THEN ISNULL(A.PREV_AM ,0) ELSE -1*ISNULL(A.PREV_AM ,0) END ) PREV_AM , '100' TYPE   
   FROM AFSFRSJ_I F INNER JOIN AFSFRG_I G ON F.CO_CD = G.CO_CD AND F.FS_TY = G.FS_TY AND F.FS_CD = G.FS_CD   
        INNER JOIN SCO SC ON F.CO_CD = SC.CO_CD   
        INNER JOIN AFSFRG_I_D H ON G.CO_CD = H.CO_CD AND G.FS_TY = H.FS_TY AND G.FS_CD = H.FS_CD AND G.LINE_NO = H.LINE_NO AND H.ACCT_TY = SC.ACCT_FG  
        INNER JOIN   
       (   
        
        SELECT  A.ACCT_CD ,S.DRCR_FG,   
         SUM( CASE WHEN S.DRCR_FG = '1' AND  A.ADR  IN  ('1','3')  THEN ISNULL(A.ACCT_AM,0)
                   WHEN S.DRCR_FG = '1' AND  A.ADR  IN  ('2','4')  THEN ISNULL(A.ACCT_AM ,0)* (-1)  
                   WHEN S.DRCR_FG = '2' AND A.ADR IN ('2','4')  THEN ISNULL(A.ACCT_AM,0)  
                   WHEN S.DRCR_FG = '2' AND A.ADR IN ('1','3')  THEN ISNULL(A.ACCT_AM,0)* (-1)  ELSE  0 END  ) ACCT_AM, 
         SUM(CASE WHEN S.DRCR_FG = '1' THEN CASE  WHEN A.ADR = '1' OR A.ADR = '3'  THEN ISNULL(A.PREV_AM,0) ELSE   ISNULL(A.PREV_AM,0) * (-1) END    
                  WHEN S.DRCR_FG = '2' THEN CASE  WHEN A.ADR = '2' OR A.ADR = '4'  THEN ISNULL(A.PREV_AM,0) ELSE   ISNULL(A.PREV_AM ,0)* (-1) END   
             ELSE 0   END) PREV_AM   
         FROM   
         ( -- 초기이월값   
         SELECT A.ACCT_AM   AS ACCT_AM,    
           A.ACCT_CD,    
           A.DRCR_FG AS ADR,   
           A.ACCT_AM  AS PREV_AM    
         FROM APREV A       INNER JOIN UFN_MULTI_PARAMETERS( @DIV_CD_STR ) B ON A.DIV_CD = B.STR_PARAMETER          
        WHERE A.CO_CD = @CO_CD  
          AND A.GISU = @GISU    
          AND ISNULL(A.ACCT_AM,0)  <> 0   
        --  AND A.DIV_CD  IN (SELECT STR_PARAMETER FROM UFN_MULTI_PARAMETERS( @DIV_CD_STR ))              
          UNION ALL  -- 전표 번호값   
        SELECT A.ACCT_AM,     
               A.ACCT_CD,              
               A.DRCR_FG AS ADR,             
               0 AS PREV_AM    
        FROM ADOCUD A   INNER JOIN UFN_MULTI_PARAMETERS( @DIV_CD_STR ) B ON A.DIV_CD = B.STR_PARAMETER             
        WHERE  A.CO_CD = @CO_CD    
         -- AND A.DIV_CD  IN (SELECT STR_PARAMETER FROM UFN_MULTI_PARAMETERS( @DIV_CD_STR ))   
          AND A.FILL_DT >=  @FILL_DT_FROM AND A.FILL_DT <= @FILL_DT_TO     
          AND ISNULL(A.ACCT_AM,0)  <> 0   
       ) A INNER JOIN SACCT S ON A.ACCT_CD = S.ACCT_CD AND S.CO_CD = @CO_CD    
       GROUP BY   A.ACCT_CD, S.DRCR_FG  
      ) A ON H.ACCT_CD = SUBSTRING(A.ACCT_CD ,1,LEN(H.ACCT_CD))   
    INNER  JOIN SACCT T ON H.CO_CD = T.CO_CD AND H.ACCT_CD = T.ACCT_CD    ---MODIFY
   WHERE F.CO_CD =@CO_CD   
   AND F.FS_TY = '20'   
   AND F.FS_CD = @FS_CD   
   GROUP BY G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, H.ID_FG,    
      CASE WHEN ISNULL(G.EXP_TY,'0') ='0' THEN SUBSTRING(A.ACCT_CD ,1,4) ELSE SUBSTRING(A.ACCT_CD,1,CAST( G.EXP_TY AS INT )) END      
   UNION ALL   
   SELECT G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, '0' ID_FG,    
      '' ACCT_CD  ,0  ACCT_AM , 0 PREV_AM, '000' TYPE   
   FROM AFSFRSJ_I F INNER JOIN AFSFRG_I G ON F.CO_CD = G.CO_CD AND F.FS_TY = G.FS_TY AND F.FS_CD = G.FS_CD   
   WHERE F.CO_CD =@CO_CD   
   AND F.FS_TY = '20'   
   AND F.FS_CD = @FS_CD   
  ) A  
  ORDER BY A.LINE_NO,A.ACCT_CD , A.TYPE   
 END   
 ELSE
   
 BEGIN -- 전체   
  
  INSERT INTO #BALANCESHEET (FS_CD, LINE_NO, DISP_NO, EXP_TY, LEVEL_CD , DISP_NM, CALC_TY, DISP_NMK, ID_FG,  ACCT_CD, ACCT_AM , PREV_AM, TYPE )  
  SELECT FS_CD, LINE_NO, DISP_NO, EXP_TY, LEVEL_CD , DISP_NM, CALC_TY, DISP_NMK, ID_FG,  ACCT_CD, ACCT_AM , PREV_AM, TYPE   
  FROM   
  (  
   SELECT G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, H.ID_FG,    
       CASE WHEN ISNULL(G.EXP_TY,'0') ='0' THEN SUBSTRING(A.ACCT_CD ,1,4) ELSE SUBSTRING(A.ACCT_CD,1,CAST( G.EXP_TY AS INT )) END ACCT_CD  , 
       SUM(CASE WHEN A.DRCR_FG = T.DRCR_FG THEN  ISNULL(A.ACCT_AM,0) ELSE -1*ISNULL(A.ACCT_AM,0) END )  ACCT_AM , 
       SUM(CASE WHEN A.DRCR_FG = T.DRCR_FG THEN ISNULL(A.PREV_AM ,0) ELSE -1*ISNULL(A.PREV_AM ,0) END ) PREV_AM , '100' TYPE   
   FROM AFSFRSJ_I F INNER JOIN AFSFRG_I G ON F.CO_CD = G.CO_CD AND F.FS_TY = G.FS_TY AND F.FS_CD = G.FS_CD   
        INNER JOIN SCO SC ON F.CO_CD = SC.CO_CD   
        INNER JOIN AFSFRG_I_D H ON G.CO_CD = H.CO_CD AND G.FS_TY = H.FS_TY AND G.FS_CD = H.FS_CD AND G.LINE_NO = H.LINE_NO AND H.ACCT_TY = SC.ACCT_FG  
        INNER JOIN   
       (SELECT  A.ACCT_CD ,S.DRCR_FG,            
          SUM( CASE WHEN S.DRCR_FG = '1' AND  A.ADR  IN  ('1','3')  THEN ISNULL(A.ACCT_AM,0)
                    WHEN S.DRCR_FG = '1' AND  A.ADR  IN  ('2','4')  THEN ISNULL(A.ACCT_AM ,0)* (-1)  
                    WHEN S.DRCR_FG = '2' AND A.ADR IN ('2','4')  THEN ISNULL(A.ACCT_AM,0)  
                    WHEN S.DRCR_FG = '2' AND A.ADR IN ('1','3')  THEN ISNULL(A.ACCT_AM,0)* (-1)  ELSE  0 END  ) ACCT_AM,         
          SUM(CASE WHEN S.DRCR_FG = '1' THEN CASE  WHEN A.ADR = '1' OR A.ADR = '3'  THEN ISNULL(A.PREV_AM,0) ELSE   ISNULL(A.PREV_AM,0) * (-1) END    
                   WHEN S.DRCR_FG = '2' THEN CASE  WHEN A.ADR = '2' OR A.ADR = '4'  THEN ISNULL(A.PREV_AM,0) ELSE   ISNULL(A.PREV_AM ,0)* (-1) END   
                   ELSE 0   END) PREV_AM   
          FROM   
          ( -- 초기이월값   
          SELECT  A.ACCT_AM  AS ACCT_AM,    
                  A.ACCT_CD,    
                  A.DRCR_FG AS ADR,   
                  A.ACCT_AM  AS PREV_AM    
          FROM APREV A       INNER JOIN UFN_MULTI_PARAMETERS( @DIV_CD_STR ) B ON A.DIV_CD = B.STR_PARAMETER          
          WHERE A.CO_CD = @CO_CD  
            AND A.GISU = @GISU    
            AND ISNULL(A.ACCT_AM,0)  <> 0   
         --  AND A.DIV_CD  IN (SELECT STR_PARAMETER FROM UFN_MULTI_PARAMETERS( @DIV_CD_STR ))              
           UNION ALL  -- 전표 번호값   
         SELECT A.ACCT_AM,     
             A.ACCT_CD,              
             A.DRCR_FG AS ADR,             
             0 AS PREV_AM    
           FROM ADOCUD A   INNER JOIN UFN_MULTI_PARAMETERS( @DIV_CD_STR ) B ON A.DIV_CD = B.STR_PARAMETER             
         WHERE  A.CO_CD = @CO_CD    
          -- AND A.DIV_CD  IN (SELECT STR_PARAMETER FROM UFN_MULTI_PARAMETERS( @DIV_CD_STR ))   
           AND A.ISU_DT >=  @FILL_DT_FROM AND A.ISU_DT <= @FILL_DT_TO     
           AND ISNULL(A.ACCT_AM,0)  <> 0   
        ) A INNER JOIN SACCT S ON A.ACCT_CD = S.ACCT_CD AND S.CO_CD = @CO_CD    
        GROUP BY   A.ACCT_CD, S.DRCR_FG   
       ) A ON H.ACCT_CD = SUBSTRING(A.ACCT_CD ,1,LEN(H.ACCT_CD))
     INNER  JOIN SACCT T ON H.CO_CD = T.CO_CD AND H.ACCT_CD = T.ACCT_CD    ---MODIFY
    WHERE F.CO_CD =@CO_CD   
    AND F.FS_TY = '20'   
    AND F.FS_CD = @FS_CD   
    GROUP BY G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, H.ID_FG,    
       CASE WHEN ISNULL(G.EXP_TY,'0') ='0' THEN SUBSTRING(A.ACCT_CD ,1,4) ELSE SUBSTRING(A.ACCT_CD,1,CAST( G.EXP_TY AS INT )) END      
    UNION ALL   
    SELECT G.FS_CD, G.LINE_NO, G.DISP_NO, G.EXP_TY, G.LEVEL_CD , G.DISP_NM, G.CALC_TY, G.DISP_NMK, '0' ID_FG,    
       '' ACCT_CD  ,0  ACCT_AM , 0 PREV_AM, '000' TYPE   
    FROM AFSFRSJ_I F INNER JOIN AFSFRG_I G ON F.CO_CD = G.CO_CD AND F.FS_TY = G.FS_TY AND F.FS_CD = G.FS_CD   
    WHERE F.CO_CD =@CO_CD   
    AND F.FS_TY = '20'   
    AND F.FS_CD = @FS_CD   
   ) A  
   ORDER BY A.LINE_NO,A.ACCT_CD , A.TYPE   
  END   
  
   
  -- CALC_TY = '1'  인것 값 설정   
  UPDATE #BALANCESHEET   
  SET ACCT_AM =  ISNULL(A.ACCT_AM,0) ,PREV_AM =  ISNULL(A.PREV_AM,0)   
  FROM ( SELECT LINE_NO ,   
    SUM( CASE WHEN ID_FG ='1' THEN 0 - ISNULL(ACCT_AM ,0) ELSE ISNULL(ACCT_AM ,0) END ) ACCT_AM ,  
    SUM( CASE WHEN ID_FG ='1' THEN 0 - ISNULL(PREV_AM ,0) ELSE ISNULL(PREV_AM ,0) END ) PREV_AM   
      FROM #BALANCESHEET  
      WHERE TYPE ='100'  
    GROUP BY LINE_NO  
     ) A   
  WHERE #BALANCESHEET.TYPE ='000'  
   AND #BALANCESHEET.CALC_TY ='1'  
   AND #BALANCESHEET.LINE_NO = A.LINE_NO      
  
  -- 항목값 설정 CALC_TY ='2'   
  DECLARE CUR_ACBC031_BALANCE CURSOR FOR   
   SELECT A.LINE_NO   
   FROM #BALANCESHEET A    
   WHERE A.TYPE ='000'  
    AND A.CALC_TY ='2'  
   ORDER BY  A.LEVEL_CD DESC,A.LINE_NO--A.LINE_NO     --R1 SHI 
  OPEN CUR_ACBC031_BALANCE     
  FETCH NEXT FROM CUR_ACBC031_BALANCE     
  INTO  @LINE_NO  
  WHILE (@@FETCH_STATUS = 0)     
  BEGIN      
   -- @LINE_NO 값 가져 오기   
   SELECT @ACCT_AM = SUM( CASE WHEN H.ID_FG ='1' THEN 0 - ISNULL(A.ACCT_AM ,0) ELSE ISNULL(A.ACCT_AM ,0) END )   ,  
     @PREV_AM = SUM( CASE WHEN H.ID_FG ='1' THEN 0 - ISNULL(A.PREV_AM ,0) ELSE ISNULL(A.PREV_AM ,0) END )     
   FROM AFSFRSJ_I F INNER JOIN AFSFRG_I G ON G.CO_CD= @CO_CD AND F.FS_TY= G.FS_TY  AND  F.FS_CD =G.FS_CD  
       INNER JOIN SCO SC ON G.CO_CD = SC.CO_CD   
       INNER JOIN AFSFRG_I_D H ON G.CO_CD = H.CO_CD AND G.FS_TY = H.FS_TY AND G.FS_CD = H.FS_CD AND G.LINE_NO = H.LINE_NO AND H.ACCT_TY = SC.ACCT_FG  
       INNER JOIN #BALANCESHEET A ON H.ACCT_CD = A.LINE_NO  
   WHERE F.CO_CD = @CO_CD   
   AND F.FS_TY ='20'  
   AND F.FS_CD =@FS_CD  
   AND G.LINE_NO = @LINE_NO  
   AND A.TYPE ='000'  
      
   -- UPDATE  항목값    
   UPDATE #BALANCESHEET   
   SET ACCT_AM = @ACCT_AM , PREV_AM = @PREV_AM  
   WHERE   TYPE ='000'  
   AND  CALC_TY ='2'  
   AND LINE_NO = @LINE_NO   
     
      
  FETCH NEXT FROM CUR_ACBC031_BALANCE   
  INTO  @LINE_NO  
  END     
  CLOSE CUR_ACBC031_BALANCE     DEALLOCATE CUR_ACBC031_BALANCE  
  
  
 -- 계정 표현 하지 않는 것 삭제  EXP_TY = '0'   
 DELETE #BALANCESHEET  
 WHERE TYPE ='100'  
 AND ISNULL( EXP_TY,'0') ='0'  
  
  
 UPDATE #BALANCESHEET   
 SET DISP_NM ='    '+A.ACCT_NM , DISP_NMK ='    '+A.ACCT_NMK, DISP_NO = ''  
 FROM ( SELECT ACCT_CD , ACCT_NM, ACCT_NMK  FROM SACCT WHERE CO_CD = @CO_CD ) A   
 WHERE TYPE ='100'  
 AND  #BALANCESHEET.ACCT_CD = A.ACCT_CD   
  
  
   
 SELECT *  
 FROM #BALANCESHEET  
 ORDER BY LINE_NO, ACCT_CD, TYPE   
  
  
END