XinYu_LZMES/.svn/pristine/18/18d4b4d4fdc4c6abb32b0abbfaeb10ee3e2efccd.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="中间库移垛">
	<query id="UIM010181_01.SELECT"  desc="中间库区域信息查询">
		<![CDATA[
		select a.row_id area_no,min(a.CLF_ROW)||'-'||max(a.CLF_ROW) area_name from (
	     select ceil(y.CLF_ROW/10) row_id, y.CLF_ROW from c_tbk08_coil_yard_area a,c_tbk08_coil_yard y
	     where a.AREA_NO = y.AREA_NO and a.area_type = 2 group by y.clf_row order by y.CLF_ROW
	     ) a group by a.row_id order by a.row_id
		 ]]>
	</query>
	<query id="UIM010181_02.SELECT"  desc="查询钢卷位置">
		<![CDATA[
		select l.cur_load_loc from c_tbl02_coil_comm l,
		c_tbk08_coil_yard y,c_tbk08_coil_yard_area a 
		where l.COIL_NO = y.COIL_NO 
		and y.AREA_NO = a.AREA_NO 
		and a.AREA_TYPE = 2 
		and l.coil_no = ?
		]]>
	</query>
	<query id="UIM010181_03.SELECT"  desc="查询区域中的垛位及钢卷">
		<![CDATA[
		select 'FALSE' chk
		,y.CLF_NAME||'-'||trim(to_char(y.CLF_ROW,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_COL,'00')) cur_load_loc
		 ,l.coil_no
		 from c_tbk08_coil_yard y,c_tbl02_coil_comm l,c_tbk08_coil_yard_area a
		where y.CLF_NAME||'-'||trim(to_char(y.CLF_ROW,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_COL,'00')) = l.CUR_LOAD_LOC(+)
		 and y.AREA_NO  = a.AREA_NO and a.AREA_TYPE = 2 and y.CLF_ROW >= ? and y.CLF_ROW <= ? and y.CLF_NAME='7'
		 order by y.CLF_ROW,y.CLF_COL,y.CLF_FL
		]]>
	</query>
	<query id="UIM010181_04.SELECT"  desc="查询移垛记录表最大主键">
		<![CDATA[
		select max(ROLL_SEQ) ROLL_SEQ from c_tbk08_coil_move
		]]>
	</query>
	<query id="UIM010181_05.SELECT"  desc="查询空垛位">
		<![CDATA[
		SELECT row_number() over(ORDER BY T.CLF_ROW, T.CLF_COL) SEQ,
			   T.CLF_ROW VROW,
		       c_pkg_uim.get_middleyardflag_byyardno(T.CLF_NO) YARD
		  FROM C_TBK08_COIL_YARD T
		 WHERE T.CLF_NAME = '2'
		   AND T.CLF_FL = 1
		   AND T.COIL_NO IS NULL
		 ORDER BY T.CLF_ROW, T.CLF_COL
		]]>
	</query>
	<query id="UIM010181_06.SELECT"  desc="查询空垛位">
		<![CDATA[
		SELECT row_number() over(ORDER BY T.CLF_ROW, T.CLF_COL) SEQ,
		       T.CLF_ROW VROW,
		       c_pkg_uim.get_middleyardflag_byyardno(T.CLF_NO) YARD
		  FROM C_TBK08_COIL_YARD T
		 WHERE T.CLF_NAME = '2'
		   AND T.CLF_FL = 1
		   AND T.COIL_NO IS NULL
		   AND T.CLF_ROW >= ? AND T.CLF_ROW <= ?
		 ORDER BY T.CLF_ROW, T.CLF_COL
		]]>
	</query>
	<query id="UIM010181_01.UPDATE"  desc="更新钢卷公共表垛位">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM L SET L.CUR_LOAD_LOC = ?
		,L.CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') WHERE L.COIL_NO = ?
		]]>
	</query>
	<query id="UIM010181_02.UPDATE"  desc="更新垛位表钢卷号">
		<![CDATA[
		UPDATE C_TBK08_COIL_YARD Y SET Y.COIL_NO = ?,Y.MOD_ID  = ?
		, Y.MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
		WHERE Y.CLF_NAME = SUBSTR(?,0,1) AND CLF_ROW = SUBSTR(?,3,2)
		AND CLF_COL = SUBSTR(?,7,2) 
		AND CLF_FL = DECODE(SUBSTR(?,5,1),'A','1','B','2','C','3','1')
		]]>
	</query>
	<query id="UIM010181_01.INSERT"  desc="插入移垛记录">
		<![CDATA[
		INSERT INTO C_TBK08_COIL_MOVE (ROLL_SEQ
		,CUR_LOAD_LOC_F,CUR_LOAD_LOC_T,COIL_NO,REG_ID
		,REG_SHIFT,REG_GROUP,REG_DTIME,REG_USE_DTIME,MOVE_TYPE)
		VALUES((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE)
		,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'00')
		]]>
	</query>
</queryMap>