XinYu_RZMES/.svn/pristine/e4/e43f496647e4145563a915351c738871c9258341.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
    <query id="UIG040041_01.select" desc="" fetchSize="10">
        <![CDATA[
SELECT    ROLL_MANA_NO||'-'|| ROLL_SLAB_SEQ ROLL_SEQ
             , a.SLAB_NO 
             , d.COIL_NO
             , SUBSTR(A.CUR_LOAD_LOC,0,6)  YARD_ADDR
             , SUBSTR(A.CUR_LOAD_LOC,7,9)  STORELAY
             , decode(MILL_SCH_STAT,'A7T','A7T','CRD','CRD',  null)  SCH_STAT  
             , a.SLAB_THK||' X '||a.SLAB_WTH||' X '||SLAB_LEN  SLAB_SIZE  
             , a.SLAB_WGT 
             , d.spec_stl_grd
             , a.HCR_CLF
             , '' CHK    
         FROM TBG02_SLAB_COMM a ,
             (SELECT b.SLAB_NO,b.COIL_NO,b.ROLL_MANA_NO,b.ROLL_SLAB_SEQ,b.spec_stl_grd   
                FROM  TBF02_MILL_IDX a,
                      TBF02_SPEC_MILL b
               WHERE a.SLAB_EDT_SEQ = b.SLAB_EDT_SEQ 
                 AND b.STATUS_CD = 'A'
                 AND L2_SEND = 'Y'        
             ) d
            WHERE a.SLAB_NO = d.SLAB_NO  
          ORDER BY  d.ROLL_MANA_NO,d.ROLL_SLAB_SEQ 
        ]]>
    </query> 
    <query id="UIG040041_02.select" desc="" fetchSize="10">
        <![CDATA[
           SELECT  SLAB_CRANE_ORDER_SEQ.NEXTVAL   FROM DUAL 
         
        ]]>
    </query> 
     <query id="UIG040041_03.select" desc="" fetchSize="10">
        <![CDATA[
          SELECT  '' CHK,CR_NO,FROM_ADDR,TO_ADDR,SLAB_CNT,REG_DTIME,SEQ_NO,ORD_SLAB_NO1 
           FROM TBG04_SLAB_CRANE_ORDER  
          WHERE ORD_STAT = '1' 
            AND ord_type = 'B' 
            order by REG_DTIME  desc
         
        ]]>
    </query> 
    <query id="UIG040041_01.insert" desc="">
        <![CDATA[
            INSERT INTO TBG04_SLAB_CRANE_ORDER
			(
			  SEQ_NO,
			  FROM_ADDR, 
			  TO_ADDR,
			  SLAB_CNT, 
			  CR_NO, 
			  ORD_STAT,
			  REG_DTIME ,
			  ORD_SLAB_NO1,
			  ORD_TYPE,
			  REG_ID  
			)
			VALUES(SLAB_CRANE_ORDER_SEQ.NEXTVAL,?,?,?,?,'2',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),?,'B','UIG040046')
          
        ]]>
    </query>
	<query id="UIG040041_ORD.update" desc="">
        <![CDATA[
            UPDATE  TBG04_SLAB_CRANE_ORDER SET 
            
               FROM_ADDR      =  ?
              ,TO_ADDR        =  ?
              ,SLAB_CNT       =  ?
              ,CR_NO          =  ? 
              ,ORD_STAT       =  '1'
              ,REG_DTIME      = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
              ,ORD_SLAB_NO1   =  ?
              ,ORD_TYPE = 'B'
            WHERE SEQ_NO = ?
        ]]>
    </query>
    <query id="UIG040041_01.delete" desc="">
        <![CDATA[
            DELETE  TBG04_SLAB_CRANE_ORDER
			WHERE SEQ_NO = ?  
          
        ]]>
    </query>
    <query id="UIG040041_01.update" desc=" 一个库存位置板坯的行车命令状态：1制定，2执行，3删除,4占位 ">
        <![CDATA[
            UPDATE  TBG04_SLAB_YARD SET
              ORD_FLAG = ?
			WHERE  SLAB_NO = ?
			  AND  YARD_ADDR = ?
			  
        ]]>
    </query>
	<query id="UIG040041_02.update" desc=" 一个库存位置板坯的行车命令状态：1制定，2执行，3删除,4占位 ">
        <![CDATA[
            UPDATE  TBG04_SLAB_YARD SET
              ORD_FLAG = ?
			WHERE  STORELAY = ?
			  AND  YARD_ADDR = ?
			  
        ]]>
    </query>
     <query id="UIG040041_MAX_STO.select" desc="" fetchSize="10">
        <![CDATA[
        SELECT
            a.STORELAY,ORD_FLAG  
        FROM 
        (     
         SELECT  STORELAY ,ORD_FLAG
           FROM TBG04_SLAB_YARD
          WHERE SLAB_NO = ?
            AND YARD_ADDR = ?
        ) a,
        (     
         SELECT  MAX(STORELAY) STORELAY 
           FROM   TBG04_SLAB_YARD
          WHERE  SLAB_NO IS NOT NULL  
            AND YARD_ADDR = ?
            AND ORD_FLAG IS NULL
        ) b 
        WHERE a.STORELAY = b.STORELAY  
        ]]>
    </query> 
    <query id="UIG040041_04.select" desc="" fetchSize="10">
        <![CDATA[
          SELECT  FROM_ADDR,ORD_SLAB_NO1
           FROM TBG04_SLAB_CRANE_ORDER  
          WHERE SEQ_NO  =  ?
         
        ]]>
    </query> 
    <query id="UIG040041_05.select" desc="" fetchSize="10">
        <![CDATA[
         SELECT  a.SLAB_NO, a.ORD_FLAG              
         FROM 
          ( 
          SELECT   SLAB_NO,STORELAY ,ORD_FLAG
              FROM   TBG04_SLAB_YARD
             WHERE  
                  SLAB_NO  IS NOT NULL  
             AND  YARD_ADDR = ?
          ) a,   
          (
            SELECT   STORELAY 
              FROM   TBG04_SLAB_YARD
             WHERE   SLAB_NO = ?
               AND   YARD_ADDR = ?
           ) b
           WHERE ORD_FLAG IS NULL
             AND  a.STORELAY > b.STORELAY
        ]]>
    </query> 
     <query id="UIG040041_SLAB_COMM.update" desc="  ">
        <![CDATA[
            UPDATE  TBG02_SLAB_COMM SET
              MILL_SCH_STAT = ? 
             ,CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') 
			WHERE  SLAB_NO = ? 
			  
        ]]>
    </query>
	<query id="UIG040042_SLAB_COMM.update" desc="  ">
        <![CDATA[
            UPDATE  TBG02_SLAB_COMM SET
              MILL_SCH_STAT = ? 
			WHERE  SLAB_NO = ? 
			  
        ]]>
    </query>
	 <query id="UIG040041_YARD_CLEAR.update" desc="  ">
        <![CDATA[
            UPDATE  TBG04_SLAB_YARD SET
              SLAB_NO = NULL 
			 ,ORD_FLAG = NULL
			 ,CR_NO = NULL
			 ,REG_ID = 'UIG040041'
             ,REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') 
            WHERE SLAB_NO   = ? 
			 AND  YARD_ADDR = ? 
              
        ]]>
    </query>
	<query id="UIG040041_SLAB_CLEAR.update" desc="  ">
        <![CDATA[
            UPDATE  TBG02_SLAB_COMM T SET
              CUR_LOAD_LOC        = 'A7T'
             ,CUR_LOAD_LOC_DTIME  = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
             ,BEF_LOAD_LOC        = CUR_LOAD_LOC
             ,BEF_LOAD_LOC_DTIME  = CUR_LOAD_LOC_DTIME
             ,SLAB_STOCK_EXT_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
             ,T.SLAB_EXIT_TYPE = (SELECT 
                                    CASE 
                                      WHEN (SYSDATE - TO_DATE( SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS'))*24*60 > 0 AND  (SYSDATE - TO_DATE( SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS'))*24*60 <= (SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD LIKE 'G04002' AND SM_CD = '1')  
                                                  THEN '1'
                                      WHEN (SYSDATE - TO_DATE( SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS'))*24*60 > (SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD LIKE 'G04002' AND SM_CD = '1') AND (SYSDATE - TO_DATE( SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS'))*24*60 <= (SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD LIKE 'G04002' AND SM_CD = '2')
                                                  THEN '2'
                                      WHEN (SYSDATE - TO_DATE( SLAB_CUT_DTIME,'YYYY-MM-DD HH24:MI:SS'))*24*60 > (SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD LIKE 'G04002' AND SM_CD = '2')
                                                  THEN '3'                       
                                    END CASE      
                                 FROM DUAL)
       		,MILL_SCH_STAT =  'A7T'
            WHERE SLAB_NO   = ? 
              
        ]]>
    </query>
	 <query id="UIG040041_CHEM_RST.select" desc="  ">
        <![CDATA[
        SELECT CHEM_CLF 
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Als',CHEM_VAL,NULL))) AL   
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'As' ,CHEM_VAL,NULL))) AS_T
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'B'  ,CHEM_VAL,NULL))) B
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Be' ,CHEM_VAL,NULL))) BE
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Bi' ,CHEM_VAL,NULL))) BI
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'C'  ,CHEM_VAL,NULL))) C
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Ca' ,CHEM_VAL,NULL))) CA   
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Ce' ,CHEM_VAL,NULL))) CE
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Co' ,CHEM_VAL,NULL))) CO
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Cr' ,CHEM_VAL,NULL))) CR
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Cu' ,CHEM_VAL,NULL))) CU
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'H'  ,CHEM_VAL,NULL))) H
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'La' ,CHEM_VAL,NULL))) LA
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Mg' ,CHEM_VAL,NULL))) MG
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Mn' ,CHEM_VAL,NULL))) MN
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Mo' ,CHEM_VAL,NULL))) MO
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'N'  ,CHEM_VAL,NULL))) N
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Nb' ,CHEM_VAL,NULL))) NB
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Ni' ,CHEM_VAL,NULL))) NI
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'O'  ,CHEM_VAL,NULL))) O
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'P'  ,CHEM_VAL,NULL))) P
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Pb' ,CHEM_VAL,NULL))) PB
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Pd' ,CHEM_VAL,NULL))) PD
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'S'  ,CHEM_VAL,NULL))) S
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Sb' ,CHEM_VAL,NULL))) SB
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Se' ,CHEM_VAL,NULL))) SE
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Si' ,CHEM_VAL,NULL))) SI
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Sn' ,CHEM_VAL,NULL))) SN
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Ta' ,CHEM_VAL,NULL))) TA
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Te' ,CHEM_VAL,NULL))) TE
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Ti' ,CHEM_VAL,NULL))) TI
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'V'  ,CHEM_VAL,NULL))) V
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'W'  ,CHEM_VAL,NULL))) W 
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Zn' ,CHEM_VAL,NULL))) ZN
       ,TO_CHAR(MAX(DECODE(CHEM_CD,'Zr' ,CHEM_VAL,NULL))) ZR  
    FROM TBB02_WORK_INGR A,
    (SELECT SUBSTR(CM.LST_INGR_CD , 3) CHEM_SEQ FROM    TBG02_CHARGE_COMM_D CM
     WHERE  CM.CHARGE_NO = ?) B,
    (SELECT MAX(CHEM_CLF) CHEM_CLF FROM TBH06_L2_MILL_INS 
     WHERE TRANS_FLAG IN('1','2') AND SLAB_NO = ? AND COIL_NO = ? )  C                                  
 
    WHERE   A.CHEM_SEQ = B.CHEM_SEQ
      AND  A.PROC_CD = 'JJ'
      AND  A.CHARGE_NO = ?
     GROUP BY CHEM_CLF 
              
        ]]>
    </query>
	<query id="UIG040041_L2_MILL_INS.update" desc="  ">
        <![CDATA[
       UPDATE  TBH06_L2_MILL_INS SET     
          MSG_FLAG       = 'N'     
         ,TRANS_FLAG     = '2'      
         ,CHEM_CLF       = '2'     
         ,CHEM_AL        = ?       
         ,CHEM_AS        = ?       
         ,CHEM_B         = ?       
         ,CHEM_BE        = ?       
         ,CHEM_BI        = ?       
         ,CHEM_C         = ?       
         ,CHEM_CA        = ?       
         ,CHEM_CE        = ?       
         ,CHEM_CO        = ?       
         ,CHEM_CR        = ?       
         ,CHEM_CU        = ?       
         ,CHEM_H         = ?       
         ,CHEM_LA        = ?       
         ,CHEM_MG        = ?       
         ,CHEM_MN        = ?       
         ,CHEM_MO        = ?       
         ,CHEM_N         = ?       
         ,CHEM_NB        = ?       
         ,CHEM_NI        = ?       
         ,CHEM_O         = ?       
         ,CHEM_P         = ?       
         ,CHEM_PB        = ?       
         ,CHEM_PD        = ?       
         ,CHEM_S         = ?       
         ,CHEM_SB        = ?       
         ,CHEM_SE        = ?       
         ,CHEM_SI        = ?       
         ,CHEM_SN        = ?       
         ,CHEM_TA        = ?       
         ,CHEM_TE        = ?       
         ,CHEM_TI        = ?       
         ,CHEM_V         = ?       
         ,CHEM_W         = ?       
         ,CHEM_ZN        = ?       
         ,CHEM_ZR        = ?    
         ,RESERVE_FIELD3      = ? 
         ,INSPECT_YN     = ?  
		 ,TRANS_DTIME     =  TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')                              
    WHERE  SLAB_NO   = ? 
	 AND   COIL_NO   = ?          
     AND TRANS_DTIME = (select  max(trans_dtime)
from TBH06_L2_MILL_INS  
WHERE  SLAB_NO   = ?)
        ]]>
    </query>
	<query id="UIG040041_L2_RHT_INS.update" desc="  ">
        <![CDATA[
       UPDATE  TBH06_L2_RHT_INS SET              
            MSG_FLAG       = 'N'             
           ,SEND_CLF        = '2'            
           ,CHEM_KIND       = '2'            
           ,CHEM_AL         = ?              
           ,CHEM_AS         = ?              
           ,CHEM_B          = ?              
           ,CHEM_BE         = ?              
           ,CHEM_BI         = ?              
           ,CHEM_C          = ?              
           ,CHEM_CA         = ?              
           ,CHEM_CE         = ?              
           ,CHEM_CO         = ?              
           ,CHEM_CR         = ?              
           ,CHEM_CU         = ?              
           ,CHEM_H          = ?              
           ,CHEM_LA         = ?              
           ,CHEM_MG         = ?              
           ,CHEM_MN         = ?              
           ,CHEM_MO         = ?              
           ,CHEM_N          = ?              
           ,CHEM_NB         = ?              
           ,CHEM_NI         = ?              
           ,CHEM_O          = ?              
           ,CHEM_P          = ?              
           ,CHEM_S          = ?              
           ,CHEM_SB         = ?              
           ,CHEM_SE         = ?              
           ,CHEM_SI         = ?              
           ,CHEM_TI         = ?              
           ,CHEM_SN         = ?              
           ,CHEM_TA         = ?              
           ,CHEM_TE         = ?              
           ,CHEM_PB         = ?              
           ,CHEM_PD         = ?              
           ,CHEM_V          = ?              
           ,CHEM_W          = ?              
           ,CHEM_ZN         = ?              
           ,CHEM_ZR         = ?  
		   ,COIL_NO         = ? 
		   ,TRANS_DTIME     =  TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')            
      WHERE  COIL_NO   = ?                    
       AND  SLAB_NO   = ?                    
       AND TRANS_DTIME = (select  max(trans_dtime)
from TBH06_L2_RHT_INS   
WHERE  SLAB_NO   = ?)  
              
        ]]>
    </query>
    <query id="UIG040041_COIL_COM.select" desc="" fetchSize="10">
        <![CDATA[
          SELECT COIL_NO, SMP_DIR_PIC_YN,OLD_SAMPL_NO 
			FROM TBH02_COIL_COMM
		  WHERE COIL_NO = ?	
         
        ]]>
    </query> 
	<query id="UIG040041_MILL_INS.select" desc="" fetchSize="10">
        <![CDATA[
         select SEQ_NO from TBH06_L2_MILL_INS
           where slab_no = ?
            and  coil_no = ?
            and  trans_FLAG IN ('1','2')
            order by TRANS_DTIME DESC
         
        ]]>
    </query> 
	<query id="UIG040041_RHF_INS.select" desc="" fetchSize="10">
        <![CDATA[
         select SEQ_NO from TBH06_L2_RHT_INS
           where slab_no = ?
            and  coil_no = ?
            and  trans_FLAG IN ('1','2')
            order by TRANS_DTIME DESC
         
        ]]>
    </query>  
	<query id="UIG040041_MILL_INS.update" desc="" fetchSize="10">
        <![CDATA[
           UPDATE TBH06_L2_MILL_INS SET
			 MSG_FLAG = 'N'
			,TRANS_FLAG = '3'
           WHERE SLAB_NO = ?
			AND  COIL_NO = ?
			AND SEQ_NO = ?
        ]]>
    </query> 
	<query id="UIG040041_RHF_INS.update" desc="" fetchSize="10">
        <![CDATA[
           UPDATE TBH06_L2_RHT_INS SET
             MSG_FLAG = 'N'
            ,TRANS_FLAG = '3'
           WHERE SLAB_NO = ?
            AND  COIL_NO = ?
            AND SEQ_NO = ?
        ]]>
    </query> 
	<query id="UIG040041_SLAB_TH.SELECT" desc="这里判断板坯是否被替换掉，被替换的板坯没有STATUS_CD状态A." fetchSize="10">
        <![CDATA[
           SELECT A.STATUS_CD FROM TBF02_SPEC_MILL A WHERE A.SLAB_NO = :1 AND A.STATUS_CD = 'A'
        ]]>
    </query> 
</queryMap>