XinYu_LZMES/.svn/pristine/08/089ddf02971658033fbcd49a4a7431a34017899a.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="中间库库存现况查询">
	<query id="UIM010150_01.SELECT"  desc="查询钢卷区域信息">
		<![CDATA[
		select a.row_id area_no,min(a.CLF_ROW)||'-'||max(a.CLF_ROW)||' 剩余垛位:'||sum(yardcount) area_name from (
		     select ceil(y.CLF_ROW/15) row_id, y.CLF_ROW
	       ,count(case when y.CLF_FL = 1 and y.COIL_NO is null then 1 else null end) yardcount  
	       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="UIM010150_02.SELECT"  desc="查询中间库垛位是否存在">
		<![CDATA[ 
		select distinct(y.clf_name||'-'||trim(to_char(y.clf_row,'00'))) clf_name
		 from c_tbk08_coil_yard y,c_tbk08_coil_yard_area a
		where a.AREA_NO = y.AREA_NO and a.AREA_TYPE = 2 and y.CLF_ROW >= ? and y.CLF_ROW <= ? order by clf_name
		 ]]>
	</query>
	<query id="UIM010150_03.SELECT"  desc="查询每行垛位数及钢卷数">
		<![CDATA[
		select y.clf_name||'-'||trim(to_char(y.clf_row,'00')) row_name,
		count(y.area_no)||'_('||count(y.coil_no)||')' row_count
		from c_tbk08_coil_yard y,c_tbk08_coil_yard_area a
		where y.area_no = a.area_no and a.area_type = ?
		group by y.clf_name,y.clf_row
		 ]]>
	</query>
	<query id="UIM010150_04.SELECT"  desc="查询钢卷总数及综合判定总数">
		<![CDATA[
		select count(y.coil_no) coil_count,
		count(l.tot_dec_grd) tot_count from
		c_tbk08_coil_yard y, c_tbl02_coil_comm l,
		c_tbk08_coil_yard_area a
		where y.coil_no = l.coil_no
		and y.area_no = a.area_no
		and a.area_type = ?
		 ]]>
	</query>
	<query id="UIM010150_05.SELECT"  desc="查询一行垛位信息">
		<![CDATA[
		select y.clf_no,y.clf_name,y.clf_col,clf_row,y.clf_fl from
		c_tbk08_coil_yard y,c_tbk08_coil_yard_area a
		where y.area_no = a.area_no
		and a.area_type = ?
		and y.clf_name||'-'||trim(to_char(y.clf_row,'00'))= ?
		 ]]>
	</query>
	<query id="UIM010150_06.SELECT"  desc="查询区域中一列的垛位中钢卷信息">
		<![CDATA[
		select y.clf_col,y.clf_fl,l.cur_load_loc, --位置
		l.coil_no,l.stl_grd,
		l.coil_outdia,l.coil_india,--外径，内径
		to_char(l.instr_coil_thk,'FM990.099')||'*'||l.instr_coil_wth it_iw,--订单规格
		to_char(l.coil_thk,'FM990.099')||'*'||l.coil_wth t_w,--实际规格
		l.act_wgt,C_PKG_UIM.GET_STDDATESTR(l.devlmt_dtime) devlmt_dtime,
		l.ord_no,l.ord_seq,
		decode(l.tot_dec_grd,'1','合格','2','不合格','3','次品','4','废品') tot_dec_grd,
		decode(l.ord_fl,'1','订单材','2','余材') ord_fl
		from c_tbk08_coil_yard y,
		c_tbl02_coil_comm l,
		c_tbk08_coil_yard_area a
		where y.coil_no = l.coil_no
		and y.area_no = a.area_no
		and a.area_type = ?
		and y.clf_name||'-'||trim(to_char(y.clf_row,'00')) = ?
		order by y.clf_col,y.clf_fl
		 ]]>
	</query>
	<query id="UIM010150_07.SELECT"  desc="查询一行中编号最小的列">
		<![CDATA[
		select  min(clf_col) clf_col from
		c_tbk08_coil_yard y,c_tbk08_coil_yard_area a
		where y.area_no = a.area_no
		and a.area_type = ?
		and y.clf_name||'-'||trim(to_char(y.clf_row,'00'))= ?
		 ]]>
	</query>
</queryMap>