XinYu_RZMES/.svn/pristine/b1/b1b084e898b21adf93dfcedc9a2020e30038aaf0.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
   <query id="UIG020210_CH.select" desc="   " fetchSize="10">
    <![CDATA[
       SELECT SEQ_NO,SLAB_RT,RST_CH_CNT,RST_CH_QTY,RST_CH_T_CNT,RST_CH_T_QTY
  
  FROM 
  ( 
   SELECT  decode(b.SLAB_RT,'H','1','P','2','Z','3','C','4','5') SEQ_NO
        ,b.SLAB_RT
        ,TO_CHAR(CH_DAY_CNT) RST_CH_CNT
        ,TO_CHAR(CH_DAY_WGT) RST_CH_QTY
        ,TO_CHAR(CH_MON_CNT) RST_CH_T_CNT
        ,TO_CHAR(CH_MON_WGT) RST_CH_T_QTY 
  
     FROM  
     (
        SELECT SLAB_RT,COUNT(CHARGE_NO)  CH_DAY_CNT,ROUND(SUM(NVL(CHARGE_WGT,0))/1000,2) CH_DAY_WGT 
          FROM TBG02_CHARGE_COMM
         WHERE  SLAB_RT IS NOT NULL
		  AND   SUBSTR(CASTING_END_DTIME,1,8) = ?
          GROUP BY SLAB_RT
     ) a,
     (
        SELECT SLAB_RT,COUNT(CHARGE_NO)  CH_MON_CNT,ROUND(SUM(NVL(CHARGE_WGT,0))/1000,2) CH_MON_WGT 
          FROM TBG02_CHARGE_COMM
         WHERE  SLAB_RT IS NOT NULL
		   AND  SUBSTR(CASTING_END_DTIME,1,8) >= SUBSTR(?,1,6) ||'01'
           AND  SUBSTR(CASTING_END_DTIME,1,8) <= ? 
         GROUP BY SLAB_RT
  
  
     ) b 
      WHERE a.SLAB_RT(+) = b.SLAB_RT
     ORDER BY SEQ_NO 
   )  
   UNION 
   SELECT '6' SEQ_NO,'S' SLAB_RT
         , TO_CHAR(CH_DAY_CNT) RST_CH_CNT
         , TO_CHAR(CH_DAY_WGT) RST_CH_QTY
         , TO_CHAR(CH_MON_CNT) RST_CH_T_CNT
         , TO_CHAR(CH_MON_WGT) RST_CH_T_QTY
    FROM     
    (
     SELECT  COUNT(CHARGE_NO)  CH_DAY_CNT,ROUND(SUM(NVL(CHARGE_WGT,0))/1000,2) CH_DAY_WGT 
          FROM TBG02_CHARGE_COMM
         WHERE SUBSTR(CASTING_END_DTIME,1,8) = ?
    ) a,
    (
       SELECT  COUNT(CHARGE_NO)  CH_MON_CNT,ROUND(SUM(NVL(CHARGE_WGT,0))/1000,2) CH_MON_WGT 
          FROM TBG02_CHARGE_COMM
         WHERE SUBSTR(CASTING_END_DTIME,1,8) >= SUBSTR(?,1,6) ||'01'
           AND  SUBSTR(CASTING_END_DTIME,1,8) <= ? 
    ) b
         
		]]>
    </query> 
	
     <query id="UIG020210_SLAB.select" desc="   " fetchSize="10">
        <![CDATA[
	   SELECT SEQ_NO,SLAB_RT,RST_SL_CNT,RST_SL_QTY,RST_SL_T_CNT,RST_SL_T_QTY
      
      FROM 
      (
       SELECT   decode(b.SLAB_RT,'H','1','P','2','Z','3','C','4','5') SEQ_NO
        , b.SLAB_RT
        ,TO_CHAR(SL_DAY_CNT) RST_SL_CNT
        ,TO_CHAR(SL_DAY_WGT) RST_SL_QTY
        ,TO_CHAR(SL_MON_CNT) RST_SL_T_CNT
        ,TO_CHAR(SL_MON_WGT) RST_SL_T_QTY 
      
  
        FROM  
        (
          SELECT SLAB_RT,COUNT(SLAB_NO)  SL_DAY_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) SL_DAY_WGT 
            FROM TBG02_SLAB_COMM
           WHERE SLAB_RT IS NOT NULL
            AND  SUBSTR(SLAB_CUT_DTIME,1,8) = ?
           GROUP BY SLAB_RT
        ) a,
        (
          SELECT SLAB_RT,COUNT(SLAB_NO)  SL_MON_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) SL_MON_WGT 
            FROM TBG02_SLAB_COMM
           WHERE SLAB_RT IS NOT NULL
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) >= SUBSTR(?,1,6) ||'01'
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) <=?
          GROUP BY SLAB_RT
  
  
        ) b    
        WHERE a.SLAB_RT(+) = b.SLAB_RT
        ORDER BY SEQ_NO
      )  
     UNION
     SELECT '6' SEQ_NO,'S' SLAB_RT 
           ,TO_CHAR(RST_SL_CNT) RST_SL_CNT
           ,TO_CHAR(RST_SL_QTY) RST_SL_QTY
           ,TO_CHAR(RST_SL_T_CNT) RST_SL_T_CNT
           ,TO_CHAR(RST_SL_T_QTY) RST_SL_T_QTY
     
     FROM 
      (
       SELECT  COUNT(SLAB_NO)  RST_SL_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) RST_SL_QTY 
            FROM TBG02_SLAB_COMM
           WHERE SLAB_RT IS NOT NULL
             AND   SUBSTR(SLAB_CUT_DTIME,1,8)  =?
          
      ) a,
      (    
       SELECT  COUNT(SLAB_NO)  RST_SL_T_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) RST_SL_T_QTY 
            FROM TBG02_SLAB_COMM
           WHERE SLAB_RT IS NOT NULL
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) >= SUBSTR(?,1,6) ||'01'
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) <=?
          
      ) b
        ]]>
    </query> 
    <query id="UIG020210_NONORD.select" desc=" " fetchSize="10">
		<![CDATA[	
		      SELECT MTL_CLF,NONORD_CNT,NONORD_QTY,NONORD_T_CNT,NONORD_T_QTY
      
      FROM
      ( 
         SELECT 
           b.MTL_CLF
              ,TO_CHAR(NONORD_CNT)   NONORD_CNT
              ,TO_CHAR(NONORD_QTY)   NONORD_QTY
              ,TO_CHAR(NONORD_T_CNT) NONORD_T_CNT
              ,TO_CHAR(NONORD_T_QTY) NONORD_T_QTY  
          FROM
  
         ( 
           SELECT MTL_CLF
                , SUM(NONORD_CNT) NONORD_CNT
                , SUM(NONORD_QTY) NONORD_QTY
           FROM
           (          
            SELECT  DECODE(NONORD_CAUSE_CD,'1A','1','2A','2','3') MTL_CLF 
                   ,COUNT(SLAB_NO) NONORD_CNT
                   ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) NONORD_QTY
              FROM TBG02_SLAB_COMM 
             WHERE  NONORD_CAUSE_CD  IS NOT NULL 
               AND  SUBSTR(SLAB_CUT_DTIME,1,8) = ?
             GROUP BY NONORD_CAUSE_CD
           )   
           GROUP BY MTL_CLF  
         )  a,
         (
            SELECT MTL_CLF
             ,SUM(NONORD_T_CNT) NONORD_T_CNT
             ,SUM(NONORD_T_QTY) NONORD_T_QTY
  
            FROM 
            (
               SELECT DECODE(NONORD_CAUSE_CD,'1A','1','2A','2','3') MTL_CLF 
                     ,COUNT(SLAB_NO) NONORD_T_CNT
                     ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) NONORD_T_QTY
                 FROM TBG02_SLAB_COMM 
                WHERE  NONORD_CAUSE_CD  IS NOT NULL 
                  AND  SUBSTR(SLAB_CUT_DTIME,1,8) >= substr(?,1,6)||'01'
                  AND  SUBSTR(SLAB_CUT_DTIME,1,8) <= ? 
                 GROUP BY NONORD_CAUSE_CD
 
            ) 
            GROUP BY  MTL_CLF
        ) b  
        WHERE a.MTL_CLF(+) = b.MTL_CLF
        ORDER BY b.MTL_CLF 
      )
      UNION 
      SELECT '4' MTL_CLF 
              ,TO_CHAR(NONORD_CNT)   NONORD_CNT
              ,TO_CHAR(NONORD_QTY)   NONORD_QTY
              ,TO_CHAR(NONORD_T_CNT) NONORD_T_CNT
              ,TO_CHAR(NONORD_T_QTY) NONORD_T_QTY 
        FROM  
        ( 
          SELECT     COUNT(SLAB_NO) NONORD_CNT
                   ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) NONORD_QTY
              FROM TBG02_SLAB_COMM 
             WHERE  NONORD_CAUSE_CD  IS NOT NULL 
               AND  SUBSTR(SLAB_CUT_DTIME,1,8) = ?
        ) a,
        (
           SELECT  COUNT(SLAB_NO) NONORD_T_CNT
                     ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) NONORD_T_QTY
                 FROM TBG02_SLAB_COMM 
                WHERE  NONORD_CAUSE_CD  IS NOT NULL 
                  AND  SUBSTR(SLAB_CUT_DTIME,1,8) >= substr(?,1,6)||'01'
                  AND  SUBSTR(SLAB_CUT_DTIME,1,8) <= ?
           
        
        )  b
                  
        
                
		]]>
	</query> 
	 <query id="UIG020210_SCRAP.select" desc=" " fetchSize="10">
		<![CDATA[	
			  
      SELECT b.MTL_CLF,TO_CHAR(SCRAP_QTY) SCRAP_QTY
            ,TO_CHAR(SCRAP_T_QTY) SCRAP_T_QTY 
        FROM
  
        (  
          SELECT DECODE(SLAB_REGEN_CLF,'2','1','3','2' ) MTL_CLF  
                ,ROUND(SUM(NVL(SCRAP_WGT,0))/1000,2) SCRAP_QTY
            FROM TBG02_SLAB_COMM 
           WHERE   SLAB_REGEN_CLF IN('2','3')
            AND   SLAB_REGEN_CLF  IS NOT NULL 
            AND  SUBSTR(SLAB_CUT_DTIME,1,8) = ?
          GROUP BY SLAB_REGEN_CLF
        )  a,
        (
   
          SELECT DECODE(SLAB_REGEN_CLF,'2','1','3','2' ) MTL_CLF  
                ,ROUND(SUM(NVL(SCRAP_WGT,0))/1000,2) SCRAP_T_QTY
            FROM TBG02_SLAB_COMM 
           WHERE   SLAB_REGEN_CLF IN('2','3')
             AND   SLAB_REGEN_CLF  IS NOT NULL 
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) >= substr(?,1,6)||'01'
             AND  SUBSTR(SLAB_CUT_DTIME,1,8) <= ?
            GROUP BY SLAB_REGEN_CLF
    
        ) b  
       WHERE a.MTL_CLF(+) = b.MTL_CLF
　　　 ORDER BY  b.MTL_CLF 　			
    
		]]>
	</query>
	  
</queryMap>