XinYu_LZMES/.svn/pristine/3c/3caa7d22965d3d76c3618cd93cd4dd7499abffc2.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="中间库盘入盘出">
	<query id="UIM010190_01.SELECT"  desc="中间库钢卷信息查询">
		<![CDATA[
		SELECT A.COIL_NO,A.SPEC_STL_GRD,to_char(A.COIL_THK,'FM990.099')||'*'||A.COIL_WTH T_W,
		to_char(A.INSTR_COIL_THK,'FM990.099')||'*'||A.INSTR_COIL_WTH IT_IW,	
		A.ORD_NO,A.ORD_SEQ,A.COIL_LEN,A.ACT_WGT,A.CAL_WGT,	
		DECODE(A.CUR_PROG_CD,'CAB','中间库入库待机','CRC','中间库充当待机','CCA','连退指示待机','CCB','连退作业待机','CCC','连退卷取待机'
		,'DBA','重卷指示待机','DBB','重卷作业待机','DCA','包装指示待机','DCB','包装作业待机'
		,'DED','综合判定待机','DFA','发货指示待机','DFB','发货待机','DFF','发货完成') CUR_PROG_CD,	
		DECODE(A.TOT_DEC_GRD,'1','合格','2','不合格','3','次品','4','废品','','待判定') TOT_DEC_GRD,
		DECODE(A.ORD_FL,'1','订单材','2','余材','','未判') ORD_FL,
		/*DECODE(A.COIL_STAT,'1','未入库','2','在库','3','出库/结束',null)*/
		 (CASE WHEN  A.COIL_STAT='1' THEN '未入库'
          WHEN  A.COIL_STAT='2' AND A.CUR_LOAD_LOC IS NULL THEN  '未入库'
          WHEN  A.COIL_STAT='2' AND A.CUR_LOAD_LOC IS NOT NULL THEN '在库'
          WHEN  A.COIL_STAT='3' THEN '出库/结束'
          END) COIL_STAT,	
		(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.COIL_IN_REG) COIL_IN_REG
		,C_PKG_UIM.GET_STDTIMESTR(A.COIL_IN_DTIME) COIL_IN_DTIME,
		(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.COIL_OUT_REG) COIL_OUT_REG
		,C_PKG_UIM.GET_STDTIMESTR(A.COIL_OUT_DTIME) COIL_OUT_DTIME
		FROM C_TBL02_COIL_COMM A  WHERE A.COIL_NO = ? 
		 ]]>
	</query>
	<query id="UIM010190_02.SELECT"  desc="查询中间库垛位是否存在">
		<![CDATA[
		SELECT A.CLF_NO FROM C_TBK08_COIL_YARD A,C_TBK08_COIL_YARD_AREA B WHERE
		A.AREA_NO = B.AREA_NO AND B.AREA_TYPE = '2'
		AND A.CLF_NAME||'-'||trim(to_char(A.CLF_ROW,'00'))||decode(A.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(A.CLF_COL,'00'))
		= ? 
		 ]]>
	</query>
	<query id="UIM010190_03.SELECT"  desc="查询钢卷号是否存在">
		<![CDATA[
		SELECT A.COIL_NO,A.COIL_STAT,
		A.SLAB_NO,a.CUR_PROG_CD,a.CUR_LOAD_LOC,
		a.TRNF_DTIME FROM C_TBL02_COIL_COMM A 
		WHERE COIL_NO = ? 
		 ]]>
	</query>
	<query id="UIM010190_04.SELECT"  desc="查询中间库垛位上是否存在钢卷">
		<![CDATA[
		SELECT A.COIL_NO FROM C_TBK08_COIL_YARD A,C_TBK08_COIL_YARD_AREA B WHERE
		A.AREA_NO = B.AREA_NO AND B.AREA_TYPE = '2'
		AND A.CLF_NAME||'-'||to_char(A.CLF_ROW,'00')||decode(A.CLF_FL,1,'A',2,'B',3,'C')||'-'||to_char(A.CLF_COL,'00')
		= ?
		 ]]>
	</query>
	<query id="UIM010190_01.UPDATE"  desc="更新钢卷公共表盘入状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM A SET 
		A.CUR_LOAD_LOC = ?,
		A.COIL_IN_REG  = ?,
		A.CUR_PROG_CD = 'CRC',
		A.CUR_PROG_CD_PGM    = 'UIM010190',
		A.CUR_PROG_CD_DTIME  = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS'),
		A.COIL_STAT = '2',
		A.ORD_FL = '2',
		A.ORD_NO = '',
		A.ORD_SEQ = '',
		A.COIL_IN_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
		A.IN_YARD_KIND = '4' --盘入
		WHERE A.COIL_NO = ?
		 ]]>
	</query>
	<query id="UIM010190_02.UPDATE"  desc="更新钢卷公共_D表盘入状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM_D A SET
		A.ORD_FL = '2',
		A.ORD_NO = '',
		A.ORD_SEQ = ''
		WHERE A.L_COIL_NO = ?
		 ]]>
	</query>
	<query id="UIM010190_03.UPDATE"  desc="钢卷盘入中间库垛位表">
		<![CDATA[
		UPDATE C_TBK08_COIL_YARD A SET
		A.COIL_NO = ?
		WHERE  A.CLF_NAME  =  substr(?,0,1)
		AND  A.CLF_COL  =  substr(?,7,2)
		AND  A.CLF_ROW  =  substr(?,3,2)
		AND  A.CLF_FL  =  DECODE(substr(?,5,1),'A','1','B','2','C','3','1')
		 ]]>
	</query>
	<query id="UIM010190_04.UPDATE"  desc="更新钢卷公共表盘出状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM A SET
		A.COIL_STAT = '3',
		A.CUR_LOAD_LOC = '',
		A.ORD_FL = '2',
		A.ORD_NO = '',
		A.ORD_SEQ = '',
		A.COIL_OUT_REG = ?,
		A.COIL_OUT_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
		A.OUT_YARD_KIND = '3' --盘出
		WHERE A.COIL_NO = ?
		 ]]>
	</query>
	<query id="UIM010190_05.UPDATE"  desc="更新钢卷公共_D表盘出状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM_D A SET
		A.ORD_FL = '2',
		A.ORD_NO = '',
		A.ORD_SEQ = ''
		WHERE A.L_COIL_NO = ?
		 ]]>
	</query>
	<query id="UIM010190_06.UPDATE"  desc="钢卷盘出中间库垛位表">
		<![CDATA[
		UPDATE C_TBK08_COIL_YARD T SET
		T.COIL_NO = '',
		T.MOD_ID = '',
		T.MOD_TIME = ''
		WHERE T.COIL_NO = ?
		 ]]>
	</query>
	<query id="UIM010190_07.UPDATE"  desc="更新钢卷公共表盘出状态">
		<![CDATA[
		UPDATE C_TBL02_COIL_COMM A SET
		A.COIL_STAT = '3',
		A.CUR_LOAD_LOC = '',
		A.ORD_FL = '2',
		A.ORD_NO = '',
		A.ORD_SEQ = '',
		A.COIL_OUT_REG = ?,
		A.COIL_OUT_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
		A.TRNF_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
		A.TRNF_REG='FEIGANG',
		A.wdlivno = ?,
		A.OUT_YARD_KIND = '5' --废钢外卖盘出操作
		WHERE A.COIL_NO = ?
		 ]]>
	</query>
</queryMap>