XinYu_RZMES/.svn/pristine/3e/3e54a1cebbe41a09d28c664de860559b588659e0.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIH050020_01.select" desc="" fetchSize="10">
		<![CDATA[
			select '' CHK 
				   ,(a.YARD_CLF||a.STEP_CLF||a.storelay||a.store_seq) YARD_ADDR
           		   ,a.coil_no OLD_SAMPL_NO
					,b.coil_no 
				   ,(b.ORD_NO||b.ORD_SEQ)ORD_NO 
				   ,b.EXTSHAPE_INSPECT_YN 
				   ,b.PRODNM_CD
				   ,b.SPM_YN
				   ,b.COIL_WTH
				   ,b.COIL_INDIA
				   ,b.COIL_OUTDIA
				   ,b.ACT_WGT
				   ,TOT_DEC_GRD
				   ,SAMPL_PICK_YN
				   ,(SELECT DISTINCT YARD_COND_DESC  FROM  TBH05_YARD_STD_SEARCH
							WHERE AREA_KIND =   ADDR_SERVICE
			                AND ROWNUM = 1 
					 )  YARD_COND_DESC
									
					  from tbh05_coil_yard a
		         		   ,TBH02_COIL_COMM b
						   ,TBH05_STD_COIL_YARD c
							where substr(PRODNM_CD,1,1) like ?||'%'
							and   yard_clf = yard_addr   
							and   yard_clf= ?
			                and   b.COIL_STAT = '2' 
							and   a.coil_no = b.OLD_SAMPL_NO(+)
		]]>
	</query> 
	<query id="UIH050020_01.insert" desc="UIH050010 INIT_PARAM" fetchSize="10">
		<![CDATA[
			/* 这里用于新增行车命令记录 */
			INSERT INTO TBH05_COIL_CRANE_ORDER (
			ORD_SEQ
			,AREA_CD
			,CR_NO
			,FROM_ADDR
			,COIL_NO
			,TO_ADDR
			,REG_ID
			,ORD_STAT
			,REG_DTIME)
			VALUES( COIL_CRANE_ORDER_SEQ.NEXTVAL
					,?
					,?
					,?
					,?
					,?
					,?
					,'1'
					,TO_CHAR(SYSDATE,'YYYYMMDDHH24MIDD')
			)
		]]>
	</query>
	<query id="UIH050020_02.select" desc="init " fetchSize="10">
		<![CDATA[
		/* 查询目的钢卷地址所在分区的 所有列的钢卷放置信息 如 每列共放多少钢卷 一层几个 二层几个 */
 			 SELECT A.YARD_ADDR,B.STORELAY1,C.STORELAY2,D.STORENUMBERS,E.STORELAY3 
			  ,'' AS RNM
			  ,'' AS OLD_SAMPL_NO
			  ,'' AS SRC_YARD_ADDR
			  ,'' AS DEST_YARD_ADDR2
			  ,'' AS CHK
			  FROM
				( SELECT YARD_ADDR FROM TBH05_STD_COIL_YARD
				WHERE SUBSTR(YARD_ADDR,1,3) = :1 
				GROUP BY YARD_ADDR
				)A,
				( SELECT YARD_CLF , COUNT(COIL_NO) STORELAY1 FROM TBH05_COIL_YARD
				  WHERE SUBSTR(YARD_CLF,1,4) like :2||'%'
				GROUP BY YARD_CLF ,STORELAY
				HAVING STORELAY = 1
				 )B,
				 (SELECT YARD_CLF , COUNT(COIL_NO) STORELAY2 FROM TBH05_COIL_YARD
				  WHERE SUBSTR(YARD_CLF,1,4) like :3||'%'
				GROUP BY YARD_CLF ,STORELAY
				HAVING STORELAY = 2
				)C,
				 ( SELECT YARD_CLF, COUNT(COIL_NO) STORENUMBERS FROM TBH05_COIL_YARD
				 WHERE SUBSTR(YARD_CLF,1,4) like :4||'%'
				 GROUP BY YARD_CLF
				 )D,
				(SELECT YARD_CLF , COUNT(COIL_NO) STORELAY3 FROM TBH05_COIL_YARD
				  WHERE SUBSTR(YARD_CLF,1,4) like :5||'%'
				GROUP BY YARD_CLF ,STORELAY
				HAVING STORELAY = 3
				)E
				WHERE A.YARD_ADDR = B.YARD_CLF(+)
				AND A.YARD_ADDR = C.YARD_CLF(+)
				AND A.YARD_ADDR = D.YARD_CLF(+)
				 AND A.YARD_ADDR = E.YARD_CLF(+)
		]]>
	</query>
	<query id="UIH050020_03.select" desc="init " fetchSize="10">
		<![CDATA[
			/* 查询钢卷库没执行的行车命令 */
			SELECT '' CHK,ORD_SEQ,FROM_ADDR,TO_ADDR,CR_NO,COIL_NO OLD_SAMPL_NO FROM TBH05_COIL_CRANE_ORDER WHERE ORD_STAT = '1' ORDER BY ORD_SEQ
		]]>
	</query>
	<query id="UIH050020_02.update" desc="更新行车命令锁定状态" fetchSize="10">
		<![CDATA[
			update tbh05_coil_crane_order set 
				REG_ID = ?,
				REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MIDD'),
				ORD_STAT = '3'  
					
			where  ORD_SEQ = ? 
		]]>
	</query>
	<query id="UIH050020_05.select" desc="查询仓库位置表，看此位置是否已经编制了行车命令 这里是处理仓库内的" fetchSize="10">
		<![CDATA[
			select RESERV_FL from tbh05_coil_yard a where yard_clf||step_clf||storelay||store_seq = ? 
		]]>
	</query>
	<query id="UIH050020_06.select" desc="查询仓库位置表，看此位置是否已经编制了行车命令 这里是处理仓库与辊道之间的" fetchSize="10">
		<![CDATA[
			select RESERV_FL,COIL_NO from tbh05_coil_yard a where yard_clf||step_clf = ?
		]]>
	</query>
	<query id="UIH050020_B2.select" desc="查询B位置的所有区域2" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT SUBSTR(YARD_CLF,2,2) LABEL , SUBSTR(YARD_CLF,2,2) VALUE  FROM TBH05_COIL_YARD WHERE YARD_CLF LIKE  ?||'%' 
		]]>
	</query>
	<query id="UIH050020_B3.select" desc="查询B位置的所有区域3和C0位置的所有区域3" fetchSize="10">
		<![CDATA[
			SELECT STEP_CLF LABEL,STEP_CLF VALUE FROM TBH05_COIL_YARD WHERE YARD_CLF LIKE ?||'%' 
		]]>
	</query>
	<query id="UIH050020_C2.select" desc="查询C位置的所有区域2和C0位置的所有区域2" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT SUBSTR(YARD_CLF,3,1) LABEL,SUBSTR(YARD_CLF,3,1) VALUE FROM TBH05_COIL_YARD WHERE YARD_CLF LIKE ?||'%'  
		]]>
	</query>
	<query id="UIH050020_C3.select" desc="查询C位置的所有区域3" fetchSize="10">
		<![CDATA[
			SELECT DISTINCT SUBSTR(YARD_CLF,4,2) LABEL ,SUBSTR(YARD_CLF,4,2) VALUE  FROM TBH05_COIL_YARD WHERE YARD_CLF LIKE ?||'%' 
		]]>
	</query>
	<query id="UIH050020_01.update" desc="更新钢卷库位置锁定状态，这里是命令制订的时候" fetchSize="10">
		<![CDATA[
			update TBH05_COIL_YARD set 
				RESERV_FL = '1'  
					
			where YARD_CLF||STEP_CLF||STORELAY||STORE_SEQ = ? 
		]]>
	</query>
	<query id="UIH050020_03.update" desc="更新钢卷库位置锁定状态，这里是命令取消的时候" fetchSize="10">
		<![CDATA[
			update TBH05_COIL_YARD set 
				RESERV_FL = ''  
					
			where YARD_CLF||STEP_CLF||STORELAY||STORE_SEQ = ? 
		]]>
	</query>
	<query id="UIH050020_04.update" desc="修改行车命令执行的行车编号" fetchSize="10">
		<![CDATA[
			update tbh05_coil_crane_order set 
				CR_NO = ?,
				REG_ID = ?,
				REG_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MIDD')   
					
			where  ORD_SEQ = ? 
		]]>
	</query>
</queryMap>