XinYu_RZMES/.svn/pristine/eb/ebff2a3ca383216a878104d206c481d1f1d38d01.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
    <query id="UIG030010_01.select" desc="??工序?程管理_select_01" fetchSize="10">
        <![CDATA[
       SELECT CHARGE_NO 
        ,PLAN_CHARGE_NO
        ,SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(INSTR_TREAT_RT,'B','BOF'),'C','CAS'),'R','RH'),'J','CCM'),'L','LF'),3) INSTR_TREAT_RT 
        ,STL_GRD
        ,CAST_SEQ
        ,PROC_CD
        ,TREAT_CD
        ,CD_DESC 
        ,TREAT_DTIME
        ,TREAT_TEMP
        ,TREAT_WGT
        ,REG_DTIME
  FROM
  (
   SELECT  a.CHARGE_NO
     ,b.PLAN_CHARGE_NO  
     ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(INSTR_RT,'B','->B'),'J','->J'),'C','->C'),'R','->R'),'L','->L') INSTR_TREAT_RT  
       ,b.STL_GRD
       ,b.CAST_EDT_CHARGE_CNT ||'-'||b.CAST_CHARGE_SEQ  CAST_SEQ 
    
       ,PROC_CD      
       ,TREAT_CD     
       ,(select SM_CFNM 
           from TBZ00_COMMCD 
          where LG_CD = 'G02001' 
            and SM_CD = TREAT_CD ) CD_DESC
       ,TREAT_DTIME
       ,TREAT_TEMP   
       ,TREAT_WGT
       ,A.REG_DTIME    
      from  TBG03_STLMAKE_PROG a,
            TBG02_CHARGE_COMM  b 
      where  a.CHARGE_NO = b.CHARGE_NO  
        AND  a.CHARGE_NO = ?
        ORDER BY TREAT_DTIME 
   )        
            
        ]]>
    </query> 
    <query id="UIG030010_02.select" desc="??工序?程管理_select_02" fetchSize="10">
        <![CDATA[
      select SM_CD
             , SM_CFNM
             , TREAT_DTIME
             , PROC_CD 
         from
          ( select SM_CD
                ,  SM_CFNM 
              from TBZ00_COMMCD    
             where   SUBSTR(SM_CD,1,1)  = ?
               AND   LG_CD = 'G02001'
           ) a ,
           (  
             select TREAT_CD,TREAT_DTIME,PROC_CD 
               from TBG03_STLMAKE_PROG
              where CHARGE_NO = ?
                
           )  b   
           where a.SM_CD = b.TREAT_CD(+) 
           order by SM_CD  
        ]]>
    </query> 
    <query id="UIG030010.CHARGE_NO.select" desc="   ">
        <![CDATA[
           SELECT  DISTINCT CHARGE_NO  
             FROM TBG03_STLMAKE_PROG  
            WHERE   CHARGE_STAT = '2'
        ]]>
    </query> 
	<query id="UIG030010.INIT_PARAM_01" desc="UIG030010 INIT_PARAM 01 select 工序? from TBZ00_COMMCD">
        <![CDATA[
       SELECT label,value,DECODE(value,'B','1','C','2','R','3','L','4','J','5',NULL) SEQ_NO
         FROM 
         (SELECT   DECODE(SUBSTR(sm_cd,1,1),'B','BOF','C','CAS','R','RH','J','CCM','L','LF',NULL)  label
           , SUBSTR(sm_cd,1,1)  value
            FROM TBZ00_COMMCD    
            WHERE LG_CD = 'G02001'
         )
         GROUP BY label,value
        ORDER BY SEQ_NO 
        ]]>
    </query> 
	<query id="UIG030010.INIT_PARAM_02" desc="UIG030010 INIT_PARAM 02 select ?次? from TBG03_STLMAKE_PROG">
        <![CDATA[
            SELECT 'J'||SUBSTR(CHARGE_MANA_NO,2,9)LABEL 
				     ,'J'||SUBSTR(CHARGE_MANA_NO,2,9) VALUE 
      		FROM   TBF01_CHARGE_IDX 
			WHERE  STATUS_CD IN('A','B')
			AND    L2_SEND = 'Y' 
			ORDER BY CHG_SEQ
			
        ]]>
    </query> 
    <query id="UIG030010.INIT_PARAM_03" desc="UIG030010 INIT_PARAM 02 select ?次? from TBG03_STLMAKE_PROG">
        <![CDATA[
            
			SELECT 'J'||SUBSTR(CHARGE_MANA_NO,2,9)LABEL 
			       ,'J'||SUBSTR(CHARGE_MANA_NO,2,9) VALUE 
			FROM   TBF01_CHARGE_IDX t1
			       ,(SELECT T2.CHARGE_NO,T2.PROC_CD FROM  tbg03_mainraw t2 
			               WHERE 1=1
			               AND SUBSTR(T2.PROC_CD,2,1) = NVL(:1,'B')  
			               GROUP BY T2.CHARGE_NO,T2.PROC_CD )T3
			WHERE  T1.REG_DTIME > TO_CHAR(SYSDATE - 10,'YYYYMMDDHH24MMSS')
			and    SUBSTR(t1.CHARGE_MANA_NO,2,9) = SUBSTR(T3.CHARGE_NO,2,9)
			AND    L2_SEND = 'Y' 
			AND    ROWNUM <= 10
			ORDER BY CHG_SEQ
        ]]>
    </query>
    <query id="UIG030010.update" desc="This step query is update query   to TBG03_STLMAKE_PROG     ">
        <![CDATA[
        
            UPDATE TBG03_STLMAKE_PROG set
              TREAT_DTIME=? 
			WHERE PROC_CD  = ?
			  AND TREAT_CD  = ?
              AND CHARGE_NO = ?
		 
        ]]>
    </query>
	<query id="UIG030010.insert" desc="This step query is update query   to TBG03_STLMAKE_PROG     ">
        <![CDATA[
            insert into tbg03_stlmake_prog (treat_dtime,treat_cd,charge_no,proc_cd,charge_stat)values(?,?,?,?,'2')
			
        ]]>
    </query>
	<query id="UIG030010.delete" desc="This step query is update query   to TBG03_STLMAKE_PROG     ">
        <![CDATA[
            delete from tbg03_stlmake_prog where treat_cd = ? 
										   and   charge_no = ? 
										   and   proc_cd = ?
        ]]>
    </query>
    <query id="UIG030010_TREAT_DTIME_UPT_01" desc="This step query is update query   to TBG03_STLMAKE_PROG     ">
        <![CDATA[
            UPDATE TBG02_CHARGE_COMM T 
			SET    T.TD_NO = ?
			       ,T.MD_NO1 = ?
			       ,T.MD_NO2 = ?
			       ,T.BOF_STA_DTIME = NVL(?,T.BOF_STA_DTIME)
			       ,T.BOF_END_DTIME = NVL(?,T.BOF_END_DTIME)
			       ,T.RH_STA_DTIME = NVL(?,T.RH_STA_DTIME)
			       ,T.RH_END_DTIME = NVL(?,T.RH_END_DTIME)
			       ,T.CAS_STA_DTIME = NVL(?,T.CAS_STA_DTIME)
			       ,T.CAS_END_DTIME = NVL(?,T.CAS_END_DTIME)
			       ,T.LF_STA_DTIME = NVL(?,T.LF_STA_DTIME)
			       ,T.LF_END_DTIME = NVL(?,T.LF_END_DTIME)
			       ,T.LT_ARRV_DTIME = NVL(?,T.LT_ARRV_DTIME)
			       ,T.CASTING_STA_DTIME = NVL(?,T.CASTING_STA_DTIME)
			       ,T.CASTING_END_DTIME = NVL(?,T.CASTING_END_DTIME)
			       ,T.RTN_CHARGE_NO = ?
			       ,T.RTN_CHARGE_WGT = ?
			       ,T.CHARGE_WGT = ?
			       ,T.BOF_TAPPING_WGT = ?
			WHERE  T.CHARGE_NO = ?   
        ]]>
    </query>
</queryMap>