XinYu_RZMES/.svn/pristine/28/28aa8d7d61c6580ba15ed204062af8e445bec23b.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="生产规范实绩值获得及判定">
  
	<query id="UIB020180_01.INITSELECT" desc="">
		SELECT T.SM_CD VALUE , T.SM_CFNM LABEL FROM TBZ00_COMMCD T WHERE T.LG_CD = 'E01002'
	</query>
     <query id="UIB020180_01.SELECT" desc="获得有对应规范标准的钢卷" fetchSize="10">  
        <![CDATA[
		  SELECT  distinct  A.COIL_NO --钢卷号
               ,
                A.OLD_SAMPL_NO --成分钢卷号
               ,
                A.SPEC_ABBSYM --标准号
               ,
                A.SPEC_STL_GRD --标准钢号
               ,
                A.ORD_NO,
                A.ORD_SEQ
		 
		  FROM (SELECT 
		       ORD.ORD_NO ORD_NO,
		       ORD.ORD_SEQ ORD_SEQ
		        FROM TBA01_ORD_LINE LIN, TBB01_ORD_TECH ORD, TBB01_ORD_HEAD OHD
		       WHERE LIN.ORD_NO = ORD.ORD_NO
		         AND LIN.ORD_SEQ = ORD.ORD_SEQ
		         AND ORD.ORD_NO = OHD.ORD_NO
		         AND ORD.ORD_SEQ = OHD.ORD_SEQ
		         AND ORD.DSN_KIND = '2') AA,  
		         TBH02_COIL_COMM A ,TBH02_MILL_RESULT B,TBF02_SPEC_MILL C
		     
		   WHERE  C.COIL_NO = B.COIL_NO
		   		 AND A.OLD_SAMPL_NO = C.COIL_NO 
		      -- AND SUBSTR(A.COIL_NO, 1, 12) = SUBSTR(C.COIL_NO, 1, 12)
		       AND C.ROLL_MANA_NO like NVL(?,'%') 
		       AND SUBSTR(A.OLD_SAMPL_NO,1,10) >= NVL(?,'R0-000000A') 
		       AND SUBSTR(A.OLD_SAMPL_NO,1,10) <= NVL(?,'R9-999999A')
		       AND AA.ORD_NO = A.ORD_NO
		       AND AA.ORD_SEQ = A.ORD_SEQ
        ]]>   
    </query>
	
	<query id="UIB020180_02.SELECT" desc="通过钢卷号获取对应的规范标准信息及实绩值">
	<![CDATA[
	 	  SELECT   A.OLD_SAMPL_NO, --钢卷号
			       AA.DSN_KIND,
			       AA.PROD_CD,
			       AA.PROD_CD_CFNM,
			       AA.PROD_TYPE,
			       AA.DCS_SYM,
			       AA.PROD_VAL,
			       
			       CASE PROD_CD_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 PROD_CD_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,
			       AA.PROD_VAL_EXP1,
			       AA.PROD_VAL_EXP2,
			       AA.PROD_VAL_EXP3,
			       AA.MEMO
			  FROM (SELECT ORD.ORD_NO ORD_NO,
			               ORD.ORD_SEQ ORD_SEQ,
			               ORD.DSN_KIND DSN_KIND,
			               ORD.PROD_CD PROD_CD,
			               SP.PROD_CD_CFNM PROD_CD_CFNM,
			               SP.DISPLAY_SEQ,
			               SUBSTR(ORD.PROD_CD, 1, 4) PROD_CD_4,
			               ORD.PROD_TYPE PROD_TYPE,
			               ORD.DCS_SYM DCS_SYM,
			               ORD.PROD_VAL PROD_VAL,
			               ORD.PROD_VAL_EXP1 PROD_VAL_EXP1,
			               ORD.PROD_VAL_EXP2 PROD_VAL_EXP2,
			               ORD.PROD_VAL_EXP3 PROD_VAL_EXP3,
			               ORD.MEMO MEMO
			          FROM TBA01_ORD_LINE LIN,
			               TBB01_ORD_TECH ORD,
			               TBB01_ORD_HEAD OHD,
			               TBB01_SEQ_PROD SP
			         WHERE LIN.ORD_NO = ORD.ORD_NO
			           AND LIN.ORD_SEQ = ORD.ORD_SEQ
			           AND ORD.ORD_NO = OHD.ORD_NO
			           AND ORD.ORD_SEQ = OHD.ORD_SEQ
			           AND ORD.PROD_CD = SP.PROD_CD
			           AND ORD.DSN_KIND = '2') AA,
			       TBH02_COIL_COMM A,
			       TBH02_MILL_RESULT B
			
			 WHERE A.OLD_SAMPL_NO = ?
			   AND A.OLD_SAMPL_NO = B.COIL_NO
			   AND AA.ORD_NO = A.ORD_NO
			   AND AA.ORD_SEQ = A.ORD_SEQ
			   ORDER BY AA.DISPLAY_SEQ
         ]]> 
	</query>
	
	<query id="UIB020180_03.SELECT" desc="判断某钢卷项目是否存在">
			SELECT  *   FROM    TBB02_DCS_TECH     T 
						WHERE      T.COIL_NO   =   ? 
						AND        T.PROD_CD   =   ? 
	</query>
	
	<query id="UIB020180_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="UIB020180_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="UIB020180_01.CALLSTATEMENT" desc="成分判定">
		{call ZL_HOST_DCS01.NIB028010(?,?,?,?,?,?,?)}
	</query>
	
	<query id="UIB020180_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>