XinYu_RZMES/.svn/pristine/dc/dc7dff042e5c8aac7cc371eca314bf9613965868.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH020210_RHF_EXT.select" desc=" 生产现况的查询，RHF EXIT SLAB   " fetchSize="10">
		<![CDATA[
	 SELECT B.SLAB_RT 
          , TO_CHAR(nvl(DAY_CNT,0)) RHF_DAY_CNT
          , TO_CHAR(nvl(DAY_WGT,0)) RHF_DAY_WGT
          , TO_CHAR(nvl(MON_CNT,0)) RHF_MON_CNT
          , TO_CHAR(nvl(MON_WGT,0)) RHF_MON_WGT 
       FROM 
        (SELECT  SLAB_RT ,COUNT(A.SLAB_NO) DAY_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) DAY_WGT
           FROM TBH02_REHEATFUR A ,
                TBG02_SLAB_COMM B 
          WHERE  A.SLAB_NO = B.SLAB_NO 
            --AND  RHF_STAT != '3'
            AND  SUBSTR(A.EXTRACT_DTIME,1,8) = ?
         GROUP BY SLAB_RT 
        ) a,
        ( 
          SELECT  SLAB_RT ,COUNT(A.SLAB_NO) MON_CNT,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) MON_WGT
            FROM  TBH02_REHEATFUR A ,
                  TBG02_SLAB_COMM B 
           WHERE  A.SLAB_NO = B.SLAB_NO 
             --AND  RHF_STAT != '3'
             AND  SUBSTR(A.EXTRACT_DTIME,1,8) >=  substr(?,1,6)||'01'  
             AND  SUBSTR(A.EXTRACT_DTIME,1,8) <= ?
           GROUP BY SLAB_RT 
        ) b   
       WHERE A.SLAB_RT(+) = B.SLAB_RT 
		]]>
	</query>
	<query id="UIH020210_COIL_RST.select" desc=" 生产现况的查询，RHF EXIT SLAB   " fetchSize="10">
        <![CDATA[
       SELECT   B.COIL_RT
              , TO_CHAR(nvl(DAY_CNT,0)) MILL_DAY_CNT
              , TO_CHAR(nvl(DAY_WGT,0)) MILL_DAY_WGT
              , TO_CHAR(nvl(MON_CNT,0)) MILL_MON_CNT
              , TO_CHAR(nvl(MON_WGT,0)) MILL_MON_WGT
         FROM 
        (   
          SELECT DECODE(SUBSTR(PRODNM_CD,1,1),'A','H','C') COIL_RT 
                ,COUNT(COIL_NO) DAY_CNT
                ,ROUND(SUM(NVL(ACT_WGT,0))/1000,2) DAY_WGT
            FROM TBH02_COIL_COMM 
           WHERE MISSNO_CLF_CD IS NULL
             AND  SUBSTR(MILL_DTIME,1,8) = ?
           GROUP BY  PRODNM_CD       
        ) A,
        (
          SELECT DECODE(SUBSTR(PRODNM_CD,1,1),'A','H','C') COIL_RT 
                ,COUNT(COIL_NO) MON_CNT
                ,ROUND(SUM(NVL(ACT_WGT,0))/1000,2) MON_WGT
            FROM TBH02_COIL_COMM 
           WHERE MISSNO_CLF_CD IS NULL
             AND  SUBSTR(MILL_DTIME,1,8) >= SUBSTR(?,1,6)||'01'
             AND  SUBSTR(MILL_DTIME,1,8) <= ? 
            GROUP BY  PRODNM_CD
        ) B   
        WHERE A.COIL_RT(+) = B.COIL_RT
  
        ]]>
    </query>
	<query id="UIH020210_SLAB_REJ.select" desc=" RHF REJECT SLAB " fetchSize="10">
		<![CDATA[
        SELECT B.SLAB_RT                                                              
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'1',DAY_CNT,NULL)),0)) ENT_DAY_CNT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'1',DAY_WGT,NULL)),0)) ENT_DAY_WGT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'1',MON_CNT,NULL)),0)) ENT_MON_CNT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'1',MON_WGT,NULL)),0)) ENT_MON_WGT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'2',DAY_CNT,NULL)),0)) EXT_DAY_CNT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'2',DAY_WGT,NULL)),0)) EXT_DAY_WGT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'2',MON_CNT,NULL)),0)) EXT_MON_CNT                 
              ,TO_CHAR(nvl(MAX(DECODE(B.MISSNO_CLF ,'2',MON_WGT,NULL)),0)) EXT_MON_WGT                 
         FROM                                                                           
        (SELECT SLAB_RT,A.MISSNO_CLF                                                   
               ,COUNT(A.SLAB_NO) DAY_CNT                                               
               ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) DAY_WGT                             
           FROM TBH02_RHF_MISSNO A,                                                     
                TBG02_SLAB_COMM  B                                                      
          WHERE  A.SLAB_NO = B.SLAB_NO                                                  
            AND SUBSTR(A.REG_DTIME,1,8)  = ?                                            
          GROUP BY SLAB_RT ,A.MISSNO_CLF                                               
        ) A,                                                                           
        (                                                                              
          SELECT SLAB_RT,A.MISSNO_CLF                                                  
                ,COUNT(A.SLAB_NO) MON_CNT                                               
                ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) MON_WGT                             
           FROM TBH02_RHF_MISSNO A,                                                     
                TBG02_SLAB_COMM  B                                                      
          WHERE  A.SLAB_NO = B.SLAB_NO                                                  
            AND SUBSTR(A.REG_DTIME,1,8) >= SUBSTR(?,1,6)||'01'                          
            AND SUBSTR(A.REG_DTIME,1,8)  <= ?                                           
           GROUP BY SLAB_RT ,A.MISSNO_CLF                                               
                                                                                
        ) B                                                                            
        WHERE A.SLAB_RT(+) = B.SLAB_RT                                                    
          AND  A.MISSNO_CLF(+) = B.MISSNO_CLF                                              
        GROUP BY B.SLAB_RT                                                            

		]]>
	</query>
	<query id="UIH020210_COIL_REJ.select" desc=" 轧回修改，仿造加回和取消。不判断   " fetchSize="10">
		<![CDATA[
		/**************************  黄次长
			SELECT   B.COIL_RT
              , TO_CHAR(DAY_CNT) DAY_CNT
              , TO_CHAR(DAY_WGT) DAY_WGT
              , TO_CHAR(MON_CNT) MON_CNT
              , TO_CHAR(MON_WGT) MON_WGT
        FROM 
        (
         SELECT DECODE(SUBSTR(A.PRODNM_CD,1,1),'A','H','C') COIL_RT 
                ,COUNT(COIL_NO) DAY_CNT
                ,ROUND(SUM(NVL(ACT_WGT,0))/1000,2) DAY_WGT
            FROM TBH02_COIL_COMM A,
                 TBG02_SLAB_COMM B 
           WHERE MISSNO_CLF_CD IS NOT NULL
             AND A.SLAB_NO = B.SLAB_NO
             AND  SUBSTR(MILL_DTIME,1,8) = ?
           GROUP BY  A.PRODNM_CD    
        ) A,
        (
           SELECT DECODE(SUBSTR(A.PRODNM_CD,1,1),'A','H','C') COIL_RT 
                ,COUNT(COIL_NO) MON_CNT
                ,ROUND(SUM(NVL(ACT_WGT,0))/1000,2) MON_WGT
            FROM TBH02_COIL_COMM A,
                 TBG02_SLAB_COMM B 
           WHERE MISSNO_CLF_CD IS NOT NULL
             AND A.SLAB_NO = B.SLAB_NO
             AND  SUBSTR(MILL_DTIME,1,8) >= SUBSTR(?,1,6) ||'01'
             AND  SUBSTR(MILL_DTIME,1,8) <= ?
           GROUP BY  A.PRODNM_CD    
        ) B  
       WHERE A.COIL_RT(+) = B.COIL_RT 
			*************************************/
		SELECT B.SLAB_RT                                                              
              ,TO_CHAR(MAX(DECODE(B.MISSNO_CLF ,'3',DAY_CNT,NULL))) DAY_CNT                 
              ,TO_CHAR(MAX(DECODE(B.MISSNO_CLF ,'3',DAY_WGT,NULL))) DAY_WGT                 
              ,TO_CHAR(MAX(DECODE(B.MISSNO_CLF ,'3',MON_CNT,NULL))) MON_CNT                 
              ,TO_CHAR(MAX(DECODE(B.MISSNO_CLF ,'3',MON_WGT,NULL))) MON_WGT      
         FROM                                                                           
        (SELECT SLAB_RT,A.MISSNO_CLF                                                   
               ,COUNT(A.SLAB_NO) DAY_CNT                                               
               ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) DAY_WGT                             
           FROM TBH02_RHF_MISSNO A,                                                     
                TBG02_SLAB_COMM  B                                                      
          WHERE  A.SLAB_NO = B.SLAB_NO                                                  
            AND SUBSTR(A.REG_DTIME,1,8)  = ?                                            
          GROUP BY SLAB_RT ,A.MISSNO_CLF                                               
        ) A,                                                                           
        (                                                                              
          SELECT SLAB_RT,A.MISSNO_CLF                                                  
                ,COUNT(A.SLAB_NO) MON_CNT                                               
                ,ROUND(SUM(NVL(SLAB_WGT,0))/1000,2) MON_WGT                             
           FROM TBH02_RHF_MISSNO A,                                                     
                TBG02_SLAB_COMM  B                                                      
          WHERE  A.SLAB_NO = B.SLAB_NO                                                  
            AND SUBSTR(A.REG_DTIME,1,8) >= SUBSTR(?,1,6)||'01'                          
            AND SUBSTR(A.REG_DTIME,1,8)  <= ?                                           
           GROUP BY SLAB_RT ,A.MISSNO_CLF                                               
                                                                                
        ) B                                                                            
        WHERE A.SLAB_RT(+) = B.SLAB_RT                                                    
          AND  A.MISSNO_CLF(+) = B.MISSNO_CLF                                              
        GROUP BY B.SLAB_RT  
		]]>
	</query>
	<query id="UIH020210_SHIP.select" desc=" SHIP DELIVERY  " fetchSize="10">
		<![CDATA[ 
        SELECT
            TO_CHAR(nvl(SHIP_DAY_CNT,0)) SHIP_DAY_CNT
           ,TO_CHAR(nvl(SHIP_DAY_WGT,0)) SHIP_DAY_WGT
           ,TO_CHAR(nvl(SHIP_MON_CNT,0)) SHIP_MON_CNT
           ,TO_CHAR(nvl(SHIP_MON_WGT,0)) SHIP_MON_WGT
		   ,TO_CHAR(nvl(COLD_DAY_CNT,0)) COLD_DAY_CNT
		   ,TO_CHAR(nvl(COLD_DAY_WGT,0)) COLD_DAY_WGT
		   ,TO_CHAR(nvl(COLD_MON_CNT,0)) COLD_MON_CNT
		   ,TO_CHAR(nvl(COLD_MON_WGT,0)) COLD_MON_WGT	 			
         FROM 
        ( 
            SELECT COUNT(COIL_NO) SHIP_DAY_CNT
                 , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     SHIP_DAY_WGT 
            FROM TBH02_COIL_COMM 
           WHERE  SUBSTR(TRNF_DTIME,1,8)  = ? 
             AND  TRNF_DTIME IS NOT NULL
        ) A,    
        (
           SELECT COUNT(COIL_NO) SHIP_MON_CNT
                , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     SHIP_MON_WGT 
             FROM TBH02_COIL_COMM 
            WHERE SUBSTR(TRNF_DTIME,1,8) >= SUBSTR(? ,1,6)||'01'
              AND  SUBSTR(TRNF_DTIME,1,8) <= ?
              AND  TRNF_DTIME IS NOT NULL  
        ) B,
		(
            SELECT COUNT(COIL_NO) COLD_DAY_CNT
                 , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     COLD_DAY_WGT 
            FROM TBH02_COIL_COMM 
           WHERE  SUBSTR(CR_PLANT_TRS_DTIME,1,8)  = ? 
             AND  CR_PLANT_TRS_DTIME IS NOT NULL
        ) C ,	
		(
           SELECT COUNT(COIL_NO) COLD_MON_CNT
                , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     COLD_MON_WGT 
             FROM TBH02_COIL_COMM 
            WHERE SUBSTR(CR_PLANT_TRS_DTIME,1,8) >= SUBSTR(? ,1,6)||'01'
              AND  SUBSTR(CR_PLANT_TRS_DTIME,1,8) <= ?
              AND  CR_PLANT_TRS_DTIME IS NOT NULL  
        ) D	
		]]>
	</query>
	<query id="UIH020210_YARD.select" desc=" YARD INVENTORY    " fetchSize="10">
        <![CDATA[ 
        SELECT COIL_RT
          ,TO_CHAR(nvl(YARD1_COIL_CNT,0)) YARD1_COIL_CNT
          ,TO_CHAR(nvl(YARD2_COIL_CNT,0)) YARD2_COIL_CNT
          ,TO_CHAR(nvl(YARD3_COIL_CNT,0)) YARD3_COIL_CNT
          ,TO_CHAR(NVL(YARD1_COIL_CNT,0)+NVL(YARD2_COIL_CNT,0)+NVL(YARD3_COIL_CNT,0)) TOT_COIL_CNT
          ,TO_CHAR(nvl(YARD1_COIL_WGT,0)) YARD1_COIL_WGT
          ,TO_CHAR(nvl(YARD2_COIL_WGT,0)) YARD2_COIL_WGT
          ,TO_CHAR(nvl(YARD3_COIL_WGT,0)) YARD3_COIL_WGT
          ,TO_CHAR(NVL(YARD1_COIL_WGT,0)+NVL(YARD2_COIL_WGT,0)+NVL(YARD3_COIL_WGT,0)) TOT_COIL_WGT
        FROM 
        ( SELECT COIL_RT
            ,MAX(DECODE(YARD_CLF,'C1',YARD_COIL_CNT,NULL)) YARD1_COIL_CNT
            ,MAX(DECODE(YARD_CLF,'C1',COIL_WGT,NULL)) YARD1_COIL_WGT 
            ,MAX(DECODE(YARD_CLF,'C2',YARD_COIL_CNT,NULL)) YARD2_COIL_CNT
            ,MAX(DECODE(YARD_CLF,'C2',COIL_WGT,NULL)) YARD2_COIL_WGT
            ,MAX(DECODE(YARD_CLF,'C3',YARD_COIL_CNT,NULL)) YARD3_COIL_CNT
            ,MAX(DECODE(YARD_CLF,'C3',COIL_WGT,NULL)) YARD3_COIL_WGT 
          FROM 
          (
            SELECT  DECODE(SUBSTR(PRODNM_CD,1,1),'A','H','C')  COIL_RT
                , SUBSTR(YARD_CLF,1,2) YARD_CLF
                , COUNT(a.COIL_NO) YARD_COIL_CNT 
                , ROUND(SUM(DECODE(ACT_WGT,null,CAL_WGT,ACT_WGT))/1000,2) COIL_WGT
             FROM TBH05_COIL_YARD a,
                  TBH02_COIL_COMM b
            WHERE 
                 a.COIL_NO = b.OLD_SAMPL_NO
             AND  SUBSTR(YARD_CLF,1,1)  = 'C'  
           GROUP BY  SUBSTR(YARD_CLF,1,2) ,DECODE(SUBSTR(PRODNM_CD,1,1),'A','H','C') 
         )  
         GROUP BY COIL_RT
       ) 
        ]]>
    </query>
	<query id="UIH020210_YC.select" desc="  余材查询  " fetchSize="10">
        <![CDATA[ 
        SELECT
            TO_CHAR(nvl(YC_DAY_CNT,0)) YC_DAY_CNT
           ,TO_CHAR(nvl(YC_DAY_WGT,0)) YC_DAY_WGT
           ,TO_CHAR(nvl(YC_MON_CNT,0)) YC_MON_CNT
           ,TO_CHAR(nvl(YC_MON_WGT,0)) YC_MON_WGT 
           ,TO_CHAR(nvl(ROUND((YC_DAY_WGT/YC_DAY_WGT_T)*100,2),0)) YC_DAY_WGT_T 
           ,TO_CHAR(nvl(ROUND((YC_MON_WGT/YC_MON_WGT_T)*100,2),0)) YC_MON_WGT_T 
         FROM 
        ( 
            SELECT COUNT(COIL_NO) YC_DAY_CNT
                 , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     YC_DAY_WGT 
            FROM TBH02_COIL_COMM 
           WHERE  SUBSTR(MILL_DTIME,1,8)  = ? 
             AND  ORD_FL = '2' 
             AND  MILL_DTIME IS NOT NULL
        ) A,    
        (
           SELECT COUNT(COIL_NO) YC_MON_CNT
                , SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     YC_MON_WGT 
             FROM TBH02_COIL_COMM 
            WHERE SUBSTR(MILL_DTIME,1,8) >= SUBSTR(? ,1,6)||'01'
              AND  SUBSTR(MILL_DTIME,1,8) <= ?
              AND  ORD_FL = '2'
              AND  MILL_DTIME IS NOT NULL  
        ) B,
        (
           SELECT SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     YC_DAY_WGT_T 
            FROM TBH02_COIL_COMM 
           WHERE  SUBSTR(MILL_DTIME,1,8)  = ?
             AND  MILL_DTIME IS NOT NULL
        ) C,
        (
         SELECT SUM(nvl(decode(ACT_WGT,null,CAL_WGT, ACT_WGT),0))     YC_MON_WGT_T 
             FROM TBH02_COIL_COMM 
            WHERE SUBSTR(MILL_DTIME,1,8) >= SUBSTR(? ,1,6)||'01'
              AND  SUBSTR(MILL_DTIME,1,8) <= ?
              AND  MILL_DTIME IS NOT NULL  
        ) D
        ]]>
    </query>
</queryMap>