XinYu_RZMES/.svn/pristine/ba/ba6676d82134a715ca75d5fa9e21974a9781808f.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="生产规范判定结果查询">
  
	<query id="UIB020181_01.INITSELECT" desc="">
		SELECT T.SM_CD VALUE , T.SM_CFNM LABEL FROM TBZ00_COMMCD T WHERE T.LG_CD = 'E01002'
	</query>
     <query id="UIB020181_01.SELECT" desc="获得有判定结果的钢卷" fetchSize="10">  
        <![CDATA[
	 	  SELECT distinct
				 TE.COIL_NO,
                A.SPEC_ABBSYM, --标准号
                A.SPEC_STL_GRD, --标准钢号
                A.ORD_NO||A.ORD_SEQ  ORD_NO ,
                TE.PROD_DCS_TP,
                TE.PROD_DCS_DTIME,
                TE.REG_ID,
                (SELECT MAX(PROD_DCS_RST)  FROM TBB02_DCS_TECH WHERE COIL_NO = TE.COIL_NO) DCS_RST --取判定结果最大值为2不合格
		   FROM TBH02_COIL_COMM A, TBB02_DCS_TECH TE
		   WHERE A.OLD_SAMPL_NO = TE.COIL_NO
			    AND TE.PROD_DCS_DTIME BETWEEN ? AND ?
			    AND SUBSTR(TE.COIL_NO, 1, 10) >= NVL(?, 'R0-000000A')
			    AND SUBSTR(TE.COIL_NO, 1, 10) <= NVL(?, 'R9-999999A')
        ]]>   
    </query>
	
	<query id="UIB020181_02.SELECT" desc="通过钢卷号获取对应判定结果">
	<![CDATA[
			SELECT TE.COIL_NO,
			       TE.PROD_CD,
			       SP.PROD_CD_CFNM,
			       SP.DCS_SYM,
			       C.PROD_VAL,
			       
			       CASE SUBSTR(TE.PROD_CD, 1, 4)
			         WHEN 'XXZX' THEN
			          B.FM_STRIP_WDG_MIN
			         WHEN 'XXZD' THEN
			          B.FM_STRIP_WDG_MAX
			         WHEN 'TDZX' THEN
			          B.FM_EXT_STRIP_CROWN_MIN
			         WHEN 'TDZD' THEN
			          B.FM_EXT_STRIP_CROWN_MAX
			         WHEN 'JQZX' THEN
			          B.DC_TEMP_MIN
			         WHEN 'JQZD' THEN
			          B.DC_TEMP_MAX
			         WHEN 'JZZX' THEN
			          B.FM_EXT_STRIP_TEMP_MIN
			         WHEN 'JZZD' THEN
			          B.FM_EXT_STRIP_TEMP_MAX
			         ELSE
			          B.FM_EXT_STRIP_THK_AVG
			       END ACTUAL_VALUE,
			       CASE SUBSTR(TE.PROD_CD, 1, 4)
			         WHEN 'XXZX' THEN
			          B.FM_STRIP_WDG_AVG
			         WHEN 'XXZD' THEN
			          B.FM_STRIP_WDG_AVG
			         WHEN 'TDZX' THEN
			          B.FM_EXT_STRIP_CROWN_AVG
			         WHEN 'TDZD' THEN
			          B.FM_EXT_STRIP_CROWN_AVG
			         WHEN 'JQZX' THEN
			          B.DC_TEMP_AVG
			         WHEN 'JQZD' THEN
			          B.DC_TEMP_AVG
			         WHEN 'JZZX' THEN
			          B.FM_EXT_STRIP_TEMP_AVG
			         WHEN 'JZZD' THEN
			          B.FM_EXT_STRIP_TEMP_AVG
			         ELSE
			          B.FM_EXT_STRIP_THK_AVG
			       END ACTUAL_AVG,
			       
			       TE.PROD_DCS_RST,
			       TE.PROD_DCS_TP
			  FROM TBB02_DCS_TECH TE
			      , TBB01_SEQ_PROD SP
			      , TBH02_COIL_COMM A
			      ,TBH02_MILL_RESULT B
			      ,TBB01_ORD_TECH C
			      
			 WHERE  TE.COIL_NO = ?
				   AND TE.PROD_CD = SP.PROD_CD
				   AND TE.COIL_NO = B.COIL_NO
				   AND TE.COIL_NO = A.OLD_SAMPL_NO
				   AND A.ORD_NO = C.ORD_NO
				   AND A.ORD_SEQ = C.ORD_SEQ
				   AND C.PROD_CD = TE.PROD_CD
				   ORDER BY SP.DISPLAY_SEQ
         ]]> 
	</query>
	
	<query id="UIB020181_03.SELECT" desc="判断某钢卷项目是否存在">
			SELECT  *   FROM    TBB02_DCS_TECH     T 
						WHERE      T.COIL_NO   =   ? 
						AND        T.PROD_CD   =   ? 
	</query>
	
	<query id="UIB020181_01.INSERT" fetchSize="" desc="生产规范判定结果录入">
		INSERT   INTO   TBB02_DCS_TECH(  
    	                COIL_NO,
		                PROD_CD,
						PROD_DCS_RST,
						PROD_DCS_TP,
						PROD_DCS_DTIME,
						REG_ID,
						REG_DTIME  
		) VALUES(?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))
	</query>
	<query id="UIB020181_01.UPDATE" fetchSize="" desc="生产规范判定结果修改">
		 UPDATE         TBB02_DCS_TECH SET 
		               	PROD_DCS_RST    = ?  --判定结果
		               ,PROD_DCS_TP     = ?  --判定类型
		               ,PROD_DCS_DTIME  = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')  --判定时间
					   ,REG_ID          = ?  --修改人
					   ,MOD_DTIME       =    TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')  --修改日期
		 WHERE          COIL_NO         = ?  --钢卷号
		 AND            PROD_CD         = ?  --规范代码
	</query>
	
	<query id="UIB020181_06.UPDATE" desc="更新ASK_TP">
	<![CDATA[
		UPDATE       TBG02_SLAB_COMM 	T
		SET          T.ASK_TP='0'
		WHERE        T.SLAB_NO=? AND (T.ASK_TP IS NULL OR T.ASK_TP!='1') AND T.SLAB_STAT='2'
	]]>
	</query>
</queryMap>