XinYu_RZMES/.svn/pristine/f7/f73fe52ed9d13516431dd625e1ff0039c14dceb2.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="STEEL MAKING QUERY ">
	<query id="UIJ010042_01.select" desc=" 查询运送指示信息 " fetchSize="10">
		<![CDATA[
			SELECT X.DLIV_DIRNO, Y.CNT CNT, Y.ACT_WGT ACT_WGT
  		FROM TBH02_FJH_DLVNO X,
       (SELECT DLIV_DIRNO, COUNT(*) AS CNT, NVL(SUM(ACT_WGT), 0) AS ACT_WGT
          FROM TBH02_COIL_COMM
         GROUP BY DLIV_DIRNO) Y
 		WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
   AND x.dliv_dirno like ? || '%'
   and to_char(x.create_time, 'yyyymmdd') between ? and ?
		]]>
	</query>
	<query id="UIJ010042_02.select" desc=" 这里的发货状态同一批次一起改变，所以取钢卷公共表中的状态 " fetchSize="10">
		<![CDATA[

SELECT '' CHK,
       A.OLD_SAMPL_NO,
       A.COIL_NO,
       DECODE(A.TOT_DEC_GRD, '1', '合格', '2', '次品', ' ') TOT_DEC_GRD,
       DECODE(A.MATRL_END_CAUSE_CD, '1', '合格', '2', '次品', ' ') MATRL_END_CAUSE_CD,
       a.instr_coil_wth COIL_WTH,
       a.instr_coil_thk COIL_THK,
       a.ORD_NO,
       a.ORD_SEQ,
       a.spec_stl_grd,
       A.COIL_OUTDIA,
       NVL(A.ACT_WGT, 0) ACT_WGT,
       NVL(A.CAL_WGT, 0) CAL_WGT,
       A.CUR_LOAD_LOC,
       A.HCOLOUR_REM
  FROM TBH02_COIL_COMM A
 WHERE a.TRANS_CAR_NO is not null
   and a.ORD_NO is null
   AND a.APPOINT_YN = '04'
		]]>
	</query>
	
	<query id="UIJ010042_04.update" desc=" 更新钢卷公共表中的车辆号和排车顺序号 " fetchSize="10">
		<![CDATA[
			update TBH02_COIL_COMM
			set 
			TRANS_CAR_NO = ?  
			,APPOINT_YN='06'
			where DLIV_DIRNO =?
			
		]]>
	</query>
	<query id="UIJ010043_01.select" desc=" 查询运送指示信息 " fetchSize="10">
		<![CDATA[
			SELECT X.DLIV_DIRNO, Y.CNT CNT, Y.ACT_WGT ACT_WGT
  		FROM TBH02_FJH_DLVNO X,
       (SELECT DLIV_DIRNO, COUNT(*) AS CNT, NVL(SUM(ACT_WGT), 0) AS ACT_WGT
          FROM TBH02_COIL_COMM
         GROUP BY DLIV_DIRNO) Y
 		WHERE X.DLIV_DIRNO = Y.DLIV_DIRNO
   AND x.dliv_dirno like ? || '%'
   AND X.LINE_TP='KP'
   and to_char(x.create_time, 'yyyymmdd') between ? and ?
		]]>
	</query>
	
	 <query id="UIJ010043_02.select" desc="  " fetchSize="10">
        <![CDATA[
        SELECT '' CHK,
       A.OLD_SAMPL_NO,
       A.SPEC_STL_GRD,
       A.COIL_THK || '*' || A.COIL_WTH T_W,
       A.INSTR_COIL_THK || '*' || A.INSTR_COIL_WTH IT_IW,
       DECODE(NVL(A.ACT_WGT, 0), 0, A.CAL_WGT, A.ACT_WGT) AS ACT_WGT,
       A.ORD_NO,
       A.ORD_SEQ,
       A.CUR_LOAD_LOC,
       DECODE(A.TOT_DEC_GRD, '1', '合格', '2', '不合格', '待判定') as TOT_DEC_GRD,
       DECODE(A.CUR_PROG_CD,
              'SED',
              '判定待机',
              'SRC',
              '充当待机',
              'SFA',
              '发货待机',
              'SFB',
              '运送待机',
              'SFF',
              '发货完成',
              null) CUR_PROG_CD,
       D.CHARGE_NO,
       B.CAS_END_DTIME,
       A.MILL_DTIME,
       
       A.HCOLOUR_REM,
       A.SPEC_ABBSYM,
       A.DLIV_DIRNO,
       XB_PACKAGE.GF_COMNNAME_FIND('A01004', A.PRODNM_CD) PRODNM_CD
  FROM TBH02_COIL_COMM     A,
       TBG02_CHARGE_COMM   b,
       TBG02_CHARGE_COMM_D D
 WHERE substr(A.SLAB_NO, 0, 10) = D.CHARGE_No
   AND substr(B.CHARGE_NO, 0, 9) = SUBSTR(D.CHARGE_NO, 0, 9)
   AND A.DLIV_DIRNO = ?
        ]]>
    </query>
	
	<query id="UIJ010043_04.update" desc=" 开平更新钢卷公共表中的车辆号和排车顺序号 " fetchSize="10">
		<![CDATA[
			update TBH02_COIL_COMM
			set 
			TRANS_CAR_NO = ?  
			,APPOINT_YN='06'
			,SHIP_PROG_CD='06'
			where DLIV_DIRNO =?
			
		]]>
	</query>
</queryMap>