XinYu_RZMES/.svn/pristine/2a/2a4cfd4daf7145f8b9e97b2f8cff34f09d8030c2.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH020036_01.select" desc="查询钢卷信息" fetchSize="10">
		<![CDATA[						                          
			 select h.OLD_SAMPL_NO COIL_NO,		   
				substr(h.OLD_SAMPL_NO, 1, 12)||'0' MONTHJUAN,
				       h.COIL_THK,
				       h.COIL_WTH,
				       h.COIL_LEN,
				       h.ACT_WGT,
				       h.coil_india,
				       h.coil_outdia,
				       h.SPEC_STL_GRD,
				       h.SLAB_NO,
				       h.ORD_NO,
				       h.ORD_SEQ,
				       h.INSTR_COIL_THK || ' * ' || h.INSTR_COIL_WTH IT_IW,
				       h.CUR_LOAD_LOC
				  from tbh02_coil_comm h
				 where 1 = 1
				   and h.OLD_SAMPL_NO = ?
		]]>
	</query>
	<query id="UIH020036_01.insert" desc="增加子卷（订单）" fetchSize="10">
		<![CDATA[
			insert into tbh02_coil_comm
			  (coil_no,
			   slab_no,
			   coil_stat,
			   COIL_CRE_DTIME,
			   cur_prog_cd,
			   cur_prog_cd_dtime,
			   cur_prog_cd_pgm,
			   instr_coil_thk,
			   instr_coil_wth,
			   instr_coil_len,
			   instr_coil_india,
			   instr_coil_outdia,
			   instr_coil_wgt,
			   devlmt_dtime,
			   dest_cd,
			   ordcust_cd,
			   cust_Cd,
			   coil_len,
			   coil_india,
			   coil_outdia,
			   coil_thk,
			   coil_wth,
			   act_wgt,
			   ord_fl,
			   ord_no,
			   ord_seq,
			   prodnm_cd,
			   spec_abbsym,
			   spec_stl_grd,
			   ord_use_cd,
			   stl_grd,
			   mill_dtime,
			   old_sampl_no,
			   reg_id,
			   reg_pgm,
			   reg_dtime,
			   SAMPL_NO,
			   SIZE_DEC_RST,
			   INGR_DEC_GRD,
			   MATLQLTY_DEC_GRD,
			   PROC_DEC_RST,
			   EXTSHAPE_DEC_GRD,
			   WGT_DEC_RST,
			   INGR_ONLY_TP,
			   SAMPL_CUT_LOC)
			  select substr(h.coil_no,1,12)|| substr(?,-1,1) coil_no,
			         h.SLAB_NO,
			         2 coil_stat,
			         to_char(sysdate, 'YYYYMMDDHH24MISS') COIL_CRE_DTIME,
			         'SED' cur_prog_cd,
			         to_char(sysdate, 'YYYYMMDDHH24MISS') cur_prog_cd_dtime,
			         'UIH020036' cur_prog_cd_pgm,
			         h.INSTR_COIL_THK,
			         h.INSTR_COIL_WTH,
			         h.INSTR_COIL_LEN,
			         h.INSTR_COIL_INDIA,
			         h.INSTR_COIL_OUTDIA,
			         h.INSTR_COIL_WGT,
			         h.DEVLMT_DTIME,
			         h.DEST_CD,
			         h.ORDCUST_CD,
			         h.CUST_CD,
			         ? coil_len,
			         ? coil_india,
			         ? coil_outdia,
			         h.COIL_THK,
			         h.COIL_WTH,
			         ? act_wgt,
			         h.ORD_FL,
			         ? ORD_NO,
			         ? ORD_SEQ,
			         h.PRODNM_CD,
			         h.SPEC_ABBSYM,
			         h.SPEC_STL_GRD,
			         h.ORD_USE_CD,
			         h.STL_GRD,
			         h.MILL_DTIME,
			         ? old_sampl_no,
			         ? reg_id,
			         'UIH020036' reg_pgm,
			         ? reg_dtime,
			         h.SAMPL_NO,
			         h.SIZE_DEC_RST,
			         INGR_DEC_GRD,
			         h.MATLQLTY_DEC_GRD,
			         h.PROC_DEC_RST,
			         --h.EXTSHAPE_DEC_GRD,
			         null,
			         h.WGT_DEC_RST,
			         h.INGR_ONLY_TP,
			         h.SAMPL_CUT_LOC
			    from tbh02_coil_comm h
			   where h.OLD_SAMPL_NO = substr(?, 1, 12) || '0'
		]]>
	</query>
	<query id="UIH020036_02.insert" desc="增加子卷_D" fetchSize="10">
		<![CDATA[
			insert into Tbh02_Coil_Comm_d
			  (COIL_NO,
			   CUT_SEQ,
			   ORD_THK,
			   ORD_WTH,
			   ORD_LEN,
			   ORD_WGT,
			   ORD_WGT_MIN,
			   ORD_WGT_MAX,
			   ORD_INDIA,
			   ORD_OUTDIA,
			   ORD_NO,
			   ORD_SEQ,
			   ORD_FL)
			  select substr(d.coil_no,1,12)|| substr(?,-1,1) coil_no,
			         substr(?, -1, 1) cut_seq,
			         d.ORD_THK,
			         d.ORD_WTH,
			         d.ORD_LEN,
			         d.ORD_WGT,
			         d.ORD_WGT_MIN,
			         d.ORD_WGT_MAX,
			         d.ORD_INDIA,
			         d.ORD_OUTDIA,
			         d.ORD_NO,
			         d.ORD_SEQ,
			         d.ORD_FL
			    from tbh02_coil_comm_d d
			   where d.COIL_NO = (select h.COIL_NO from tbh02_coil_comm h where h.OLD_SAMPL_NO = substr(?,1,12)||'0')
			   and rownum = '1'

		]]>
	</query>
	
	<query id="UIH020036_02.select" desc="查询钢卷" fetchSize="10">
		<![CDATA[
			select d.COIL_NO from tbh02_coil_comm_d d where d.COIL_NO = (select h.COIL_NO from tbh02_coil_comm h where h.OLD_SAMPL_NO = ?) and d.CUT_SEQ = '0'|| substr(?,-1,1)
		]]>
	</query>
	
	<query id="UIH020036_03.select" desc="查询母卷堆位" fetchSize="10">
		<![CDATA[
			select CUR_LOAD_LOC from tbh02_coil_comm where OLD_SAMPL_NO = substr(?,1,12)||'0'
		]]>
	</query>
	
	<query id="UIH020036_05.select" desc="查询钢卷是否存在" fetchSize="10">
		<![CDATA[
			select OLD_SAMPL_NO from tbh02_coil_comm where OLD_SAMPL_NO = ?
		]]>
	</query>
	<query id="UIH020036_07.select" desc="查询钢卷的材料区分" fetchSize="10">
		<![CDATA[
			select ORD_FL from tbh02_coil_comm where OLD_SAMPL_NO = substr(?,1,12)||'0'
		]]>
	</query>
	<query id="UIH020036_08.select" desc="查询订单号合同号" fetchSize="10">
		<![CDATA[
			 select ORD_NO,ORD_SEQ from tbf05_spec_mill  where COIL_NO = substr(?,1,12)||'0'
		]]>
	</query>
	<query id="UIH020036_01.update" desc="修改母卷信息" fetchSize="10">
		<![CDATA[
			UPDATE TBH02_COIL_COMM H
				   SET H.COIL_STAT          = '3',
				       H.BEF_PROG_CD        = H.CUR_PROG_CD,
				       H.BEF_PROG_CD_DTIME  = H.CUR_PROG_CD_DTIME,
				       H.BEF_PROG_CD_PGM    = H.CUR_PROG_CD_PGM,
				       H.CUR_PROG_CD        = 'SGF',
				       H.CUR_PROG_CD_DTIME  = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
				       H.CUR_PROG_CD_PGM    = 'UIH020036',
				       H.BEF_LOAD_LOC       = H.CUR_LOAD_LOC,
				       H.TRNF_SHIFT         = '',
				       H.TRNF_GROUP         = '',
				       H.TRNF_REG           = ?,
				       H.CUR_LOAD_LOC       = 'SL',
				       H.TRNF_DTIME         = ?,
				       H.TRNF_USE_TIME      = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
				       H.CR_PLANT_TRS_CLF   = 'SL',
				       H.CR_PLANT_TRS_NO    = 'SL',
				       H.CR_PLANT_TRS_DTIME = '',
				       H.DLIV_TP            = '',
				       H.CR_PLANT_TRS_SEQ   = '',
				       H.CR_PLANT_TRS_FLAG  = '',
				       H.SHIP_PROG_CD		= 'SL',
				       H.SHIP_DIRNO			= '',
				       H.DLIV_DIRNO			= '',
				       H.ISDIVI			    = '0'
				 WHERE H.OLD_SAMPL_NO =  substr(?,1,12)||'0'				 
		]]>
	</query>
	
	<query id="UIH020036_02.update" desc="清空母卷热轧库垛位">
		<![CDATA[
			UPDATE TBH05_COIL_YARD T SET 
				   T.COIL_NO = '',
				   T.REG_ID = '',
				   T.REG_DTIME = '' 
                    where t.YARD_CLF = substr(?,0,5) 
                    and t.STEP_CLF = substr(?,6,2)
                    and t.STORELAY = substr(?,8,1)
                    and t.STORE_SEQ = substr(?,9,1)  
		]]>
	</query>
	
	<query id="UIH020036_05.update" desc="修改平整卷信息">
		<![CDATA[
			update tbh02_coil_comm h
			set h.coil_len = ?,
			    h.COIL_INDIA = ?,
			    h.COIL_OUTDIA = ?,
			    h.ACT_WGT = ?,
			    h.REG_ID = ?,
			    h.REG_PGM = ?,
			    h.ORD_NO  = ?,
			    h.ORD_SEQ = ?,
			    h.ORD_FL = '1'
    		where h.OLD_SAMPL_NO = ? 
		]]>
	</query>
	
	<query id="UIH020036_06.update" desc="将计划子表的状态该为D">
		<![CDATA[		
			update tbf05_spec_mill  set STATUS_CD = 'D' where COIL_NO = substr(?,1,12)||'0' and STATUS_CD <> 'D'
		]]>
	</query>
	
	<query id="UIH020036_07.update" desc="分析计划总表的状态">
		<![CDATA[		
			update TBF05_SPEC_SPM
			   set STATUS_CD = 'D'
			 where SPM_MANA_NO = (select t.SPM_MANA_NO
			                        from tbf05_spec_mill t
			                       where t.COIL_NO = substr(?,1,12)||'0')
			   AND STATUS_CD <> 'D'
			   and (select count(COIL_NO)
			          from tbf05_spec_mill
			         where SPM_MANA_NO =
			               (select t.SPM_MANA_NO
			                  from tbf05_spec_mill t
			                 where t.COIL_NO = substr(?,1,12)||'0')) =
			       (select count(COIL_NO)
			          from tbf05_spec_mill
			         where SPM_MANA_NO =
			               (select t.SPM_MANA_NO
			                  from tbf05_spec_mill t
			                 where t.COIL_NO = substr(?,1,12)||'0')
			           and (STATUS_CD = 'D' OR STATUS_CD = 'E'))
		]]>
	</query>
	
	<query id="UIH020036_06.insert" desc="增加子卷轧制记录" fetchSize="10">
    <![CDATA[
      insert into TBH02_MILL_RESULT
        (COIL_NO,
         FM_EXT_STRIP_THK_AVG,
         FM_EXT_STRIP_TEMP_AVG,
         DC_TEMP_AVG,
         FM_EXT_STRIP_CROWN_AVG)
        select substr(d.coil_no,1,12)|| substr(?,-1,1) coil_no,
               d.FM_EXT_STRIP_THK_AVG,
               d.FM_EXT_STRIP_TEMP_AVG,
               d.DC_TEMP_AVG,
               d.FM_EXT_STRIP_CROWN_AVG   
          from TBH02_MILL_RESULT d
         where d.COIL_NO = (select h.COIL_NO from tbh02_coil_comm h where h.OLD_SAMPL_NO = substr(?,1,12)||'0')

    ]]>  
  </query>
  
  <query id="UIH020036_07.insert" desc="增加子卷(余材)" fetchSize="10">
		<![CDATA[
			insert into tbh02_coil_comm
			  (coil_no,
			   slab_no,
			   coil_stat,
			   COIL_CRE_DTIME,
			   cur_prog_cd,
			   cur_prog_cd_dtime,
			   cur_prog_cd_pgm,
			   instr_coil_thk,
			   instr_coil_wth,
			   instr_coil_len,
			   instr_coil_india,
			   instr_coil_outdia,
			   instr_coil_wgt,
			   devlmt_dtime,
			   dest_cd,
			   ordcust_cd,
			   cust_Cd,
			   coil_len,
			   coil_india,
			   coil_outdia,
			   coil_thk,
			   coil_wth,
			   act_wgt,
			   ord_fl,
			   ord_no,
			   ord_seq,
			   prodnm_cd,
			   spec_abbsym,
			   spec_stl_grd,
			   ord_use_cd,
			   stl_grd,
			   mill_dtime,
			   old_sampl_no,
			   reg_id,
			   reg_pgm,
			   reg_dtime,
			   SAMPL_NO,
			   SIZE_DEC_RST,
			   INGR_DEC_GRD,
			   MATLQLTY_DEC_GRD,
			   PROC_DEC_RST,
			   EXTSHAPE_DEC_GRD,
			   WGT_DEC_RST,
			   INGR_ONLY_TP,
			   SAMPL_CUT_LOC,
			   TOT_DEC_GRD)
			  select substr(h.coil_no,1,12)|| substr(?,-1,1) coil_no,
			         h.SLAB_NO,
			         2 coil_stat,
			         to_char(sysdate, 'YYYYMMDDHH24MISS') COIL_CRE_DTIME,
			         'SRC' cur_prog_cd,
			         to_char(sysdate, 'YYYYMMDDHH24MISS') cur_prog_cd_dtime,
			         'UIH020036' cur_prog_cd_pgm,
			         h.INSTR_COIL_THK,
			         h.INSTR_COIL_WTH,
			         h.INSTR_COIL_LEN,
			         h.INSTR_COIL_INDIA,
			         h.INSTR_COIL_OUTDIA,
			         h.INSTR_COIL_WGT,
			         h.DEVLMT_DTIME,
			         h.DEST_CD,
			         h.ORDCUST_CD,
			         h.CUST_CD,
			         ? coil_len,
			         ? coil_india,
			         ? coil_outdia,
			         h.COIL_THK,
			         h.COIL_WTH,
			         ? act_wgt,
			         '2' ORD_FL,
			         ? ORD_NO,
			         ? ORD_SEQ,
			         h.PRODNM_CD,
			         h.SPEC_ABBSYM,
			         h.SPEC_STL_GRD,
			         h.ORD_USE_CD,
			         h.STL_GRD,
			         h.MILL_DTIME,
			         ? old_sampl_no,
			         ? reg_id,
			         'UIH020036' reg_pgm,
			         ? reg_dtime,
			         h.SAMPL_NO,
			         h.SIZE_DEC_RST,
			         INGR_DEC_GRD,
			         h.MATLQLTY_DEC_GRD,
			         h.PROC_DEC_RST,
			         --h.EXTSHAPE_DEC_GRD,
			         null,
			         h.WGT_DEC_RST,
			         h.INGR_ONLY_TP,
			         h.SAMPL_CUT_LOC,
			         '2' TOT_DEC_GRD
			    from tbh02_coil_comm h
			   where h.OLD_SAMPL_NO = substr(?, 1, 12) || '0'
		]]>
	</query>
</queryMap>