W_KMR01_11 ---- KMM13BR COPYWRITE BY LD 2000/09/07 15:30 

  SELECT DISTINCT SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) AS BGT_TYPE,   
         ( CASE SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 )
		 WHEN "09" THEN "1" WHEN "10" THEN "2" 
          WHEN "11" THEN "3" ELSE "9" END ) AS BGT_CLASS,   
         SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) AS BGT_TEAM,   

         ( ISNULL( (SELECT SUBSTRING(KMC101.KMC101_NAME,CHARINDEX
		 ("-",KMC101.KMC101_NAME)+1,12) FROM KMC101  
                     WHERE ( KMC101.KMC101_TYPE = "BUDGETCODE" ) 
                       AND ( KMC101.KMC101_CODE = SUBSTRING(
		KMM102.KMM102_BGT_TYPE,1,4 ) + "00" ) ),"" ) ) AS BGT_NAME,   
         SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) AS BGT_AMT01,   
         SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) AS BGT_AMT02,   
         SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) AS BGT_AMT03,   
         SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) AS BGT_AMT04,   
         SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) AS BGT_AMT05,   
         SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) AS BGT_AMT06,   
         SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) AS BGT_AMT07,   
         SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) AS BGT_AMT08,   
         SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) AS BGT_AMT09,   
         SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) AS BGT_AMT10,   
         SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) AS BGT_AMT11,   
         SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) AS BGT_AMT12,

         ( SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) + 
		 SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) + 
           SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) + 
		   SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) + 
           SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) + 
		   SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) + 
           SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) + 
		   SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) + 
           SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) + 
		   SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) + 
           SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) + 
		   SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) ) AS AMT01,  
         ( "XXXXXXXX" ) AS AMT02,  

         SUM( ISNULL( ( CASE WHEN ( CONVERT(CHAR(8),
		 KMM102.KMM102_CHK_DATE,112) < "YYYYMMDD" ) 
                             THEN KMM102.KMM102_CHK_AMT ELSE 0 END ),0 ) ) AS AMT09,  
         SUM( ISNULL( ( CASE WHEN ( ( CONVERT(CHAR(8),
		 KMM102.KMM102_OVER_DATE,112) < "YYYYMMDD" )
                              AND KMM102.KMM102_OVER_STATUS <> "00" ) 
                             THEN KMM102.KMM102_OVER_AMT 
                             WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) 
							 < "YYYYMMDD" )
                              AND KMM102.KMM102_OVER_STATUS = "00" ) 
                             THEN KMM102.KMM102_OVER_AMT ELSE 0 END ),0 ) ) AS AMT07,  
         SUM( ISNULL( ( SELECT CASE WHEN ( (
		 CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" )
                                     AND KMM103.KMM103_PAY_STATUS <> "2" )
                                    THEN KMM103.KMM103_PAY_AMT ELSE 0 END 
                                    FROM KMM103
                                   WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )  
                                     AND ( KMM103.KMM103_BGT_KIND =
	KMM102.KMM102_BGT_KIND )  
                                     AND ( KMM103.KMM103_CASE_NO =
		KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT03,   
         SUM( ISNULL( ( SELECT CASE WHEN ( (
		 CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" )
                                     AND KMM103.KMM103_PAY_STATUS <> "1" )
                                    THEN KMM103.KMM103_PAY_AMT ELSE 0 END 
                                    FROM KMM103
                                   WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )  
       AND ( KMM103.KMM103_BGT_KIND =
		KMM102.KMM102_BGT_KIND )  
         AND ( KMM103.KMM103_CASE_NO =
		KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT04   
    FROM KMM102,   
         KMM101  
   WHERE ( KMM102.KMM102_YY = "YYY" ) AND 
         ( KMM102.KMM102_BGT_KIND = "1" ) AND 
         ( KMM101.KMM101_YY =* KMM102.KMM102_YY ) AND 
         ( KMM101.KMM101_BGT_KIND =* KMM102.KMM102_BGT_KIND ) AND 
         ( KMM101.KMM101_BGT_TYPE =* KMM102.KMM102_BGT_TYPE )
GROUP BY SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ),  
         SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ),  
         SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 )   
ORDER BY BGT_CLASS ASC,   
         BGT_TYPE DESC  
;