XinYu_RZMES/.svn/pristine/0c/0c50a0ef8cccddc0f9bcb779ed610b2f4a165724.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="厂内成分参数录入">
	<query id="UIB010050_01.SELECT" desc="查询所有钢号" fetchSize="10">
        <![CDATA[
			SELECT    DISTINCT
			FAC_STL_GRD     LABEL
		   ,FAC_STL_GRD     VALUE
		    FROM      TBD00_STLGRD
			ORDER  BY FAC_STL_GRD
        ]]>
    </query>
		
	<query id="UIB010050_02.SELECT" desc="模糊查询（按钢号）" fetchSize="10">
        <![CDATA[
       SELECT     DISTINCT  
                  T.FAC_STL_GRD
                 ,T.FAC_CODE
                 ,T.FAC_STL_GRD     HIDDENGRD
                 ,T.FAC_CODE        HIDDENFAC 
		FROM      TBB01_FAC_INGR    T    
		WHERE     T.FAC_STL_GRD     LIKE  ?||'%'
		ORDER  BY     T.FAC_STL_GRD
			
        ]]>
    </query>
	
	<query id="UIB010050_03.SELECT" desc="按datagrid1中的钢号和生产线进行查询--非复合成分">
		SELECT  
		  a.CHEM_CD
		, decode(a.COMP_YN , 'N' , NULL , 'Y' ) COMP_YN 
		, b.CHEM_AIM
		, b.CHEM_MAX 
		, b.CHEM_MIN 
		, decode(b.CHEM_CD , null, 'y') EX   --若在客户表中不存在该元素则EX的值为y
		FROM 
		(
			SELECT  * FROM TBB01_SEQ_INGR WHERE COMP_YN  = 'N'   --限定为非复合成分
		) a 
		LEFT OUTER JOIN  TBB01_FAC_INGR b 
		ON  a.CHEM_CD   =  b.CHEM_CD  
		AND FAC_STL_GRD = ?     --按隐藏的客户公司代码查询（界面UIB010050的表1中的）
		AND FAC_CODE    = ?
		ORDER BY decode(b.CHEM_AIM||b.CHEM_MAX || b.CHEM_MIN, null, 1, 0), a.DISPLAY_SEQ
	</query>
	
	<query  id="UIB010050_04.SELECT" desc="按datagrid1中的钢号和生产线进行查询--复合成分">
		 SELECT  
		  a.CHEM_CD  
        , decode(a.COMP_YN , 'N' , NULL , 'Y' ) COMP_YN
		, a.COMP_DETAIL
		, a.COMP_CAL
		, b.CHEM_AIM
		, b.CHEM_MAX 
		, b.CHEM_MIN 
		, decode(b.CHEM_CD , null, 'y') EX   --若在客户表中不存在该元素则EX的值为y
		 FROM 
		 (
			 SELECT  * FROM TBB01_SEQ_INGR WHERE COMP_YN ='Y'  --限定为复合成分,pcm,ceq表达式
		 ) a 
		 LEFT OUTER JOIN  TBB01_FAC_INGR b 
		 ON  a.CHEM_CD   =  b.CHEM_CD  
		 AND FAC_STL_GRD = ?
		 AND FAC_CODE    = ?
		 ORDER BY b.CHEM_AIM||b.CHEM_MAX||b.CHEM_MIN , a.DISPLAY_SEQ
	</query>
	
	<query  id="UIB010050_05.SELECT" desc="新增厂内钢号时获取TBB01_SEQ_INGR中的所有非复合成分">
			SELECT 
			     CHEM_CD  
		    , '' COMP_YN
		    , '' CHEM_AIM
		    , '' CHEM_MIN
		    , '' CHEM_MAX
		    , DISPLAY_SEQ  --元素显示序号
			FROM
			TBB01_SEQ_INGR WHERE COMP_YN != 'Y' 
	     	ORDER BY  DISPLAY_SEQ
	</query>
	
	<query   id="UIB010050_06.SELECT" desc="新增厂内钢号时获取TBB01_SEQ_INGR中的所有复合成分">
		 SELECT 
		 CHEM_CD   --成分
		 , COMP_YN
		 , COMP_DETAIL  --详细内容
		 , COMP_CAL  --复合内容
		 , DISPLAY_SEQ  --元素显示序号
		 , '' CHEM_AIM
		 , '' CHEM_MIN
		 , '' CHEM_MAX
		 FROM
		 TBB01_SEQ_INGR WHERE COMP_YN ='Y' 
		 ORDER BY  DISPLAY_SEQ
	</query>
	
	<query id="UIB010050_07.SELECT" desc="">
		 SELECT       T.FAC_DETAIL 
		 FROM         TBB01_FAC_INGR T 
		 WHERE        T.FAC_STL_GRD        =     ? 
		 AND          T.FAC_CODE           =     ?   
		 AND          T.CHEM_CD            =     'C'
	</query>
	
	<query   id="UIB010050_01.UPDATE" desc="更新厂内钢号信息">
			UPDATE TBB01_FAC_INGR SET
		    FAC_STL_GRD = ?   --更新后的厂内钢号信息 
			,FAC_CODE = ?     --更新后的生产线
		    WHERE FAC_STL_GRD = ?  -- 更新前的厂内钢号信息
			AND FAC_CODE = ?  --生产线
	</query>
	
	<query   id="UIB010050_02.UPDATE" desc="更新成分最大、最小值、目标值信息">
			UPDATE TBB01_FAC_INGR SET 
		       CHEM_AIM = ?    --成分目标值
			,  CHEM_MIN = ?    --成分最小值
			,  CHEM_MAX = ?    --成分最大值
			WHERE FAC_STL_GRD = ?  --厂内钢号
			AND CHEM_CD = ?    --成分
		    AND FAC_CODE = ?   --生产线
	</query>
	
	<query   id="UIB010050_01.DELETE" desc="删除厂内钢号信息">
			DELETE FROM TBB01_FAC_INGR WHERE FAC_STL_GRD = ? AND FAC_CODE = ?
	</query>
	
	<query   id="UIB010050_01.INSERT" desc="插入新的厂内成分元素">
			INSERT INTO TBB01_FAC_INGR(
			 FAC_STL_GRD   --厂内钢号
		    ,FAC_CODE     --生产线
			,CHEM_CD      --成分
			,COMP_YN      --复合元素区分
			,CHEM_AIM     --成分目标值
			,CHEM_MIN     --成分最小值
			,CHEM_MAX     --成分最大值
		  --,FAC_TP       --生产线
			) VALUES(?,?,?,?,?,?,?)
	</query>
	
	<query id="UIB010050_01.CALLSTATEMENT" desc="厂内牌号发生变动时，发送信息到L2表格中">
		   {call NIB015051(?,?,?,?)}
	</query>
	
	<query   id="UIB010050_02.DELETE" desc="删除厂内成分信息">
			DELETE FROM TBB01_FAC_INGR WHERE FAC_STL_GRD = ? AND FAC_CODE = ? AND CHEM_CD = ?
	</query>
	
	<query   id="UIB010050.CHECK" desc="验证钢号是否存在">
SELECT  
DECODE((SELECT COUNT(*) CNT 
		FROM tbb01_fac_ingr  t 
		WHERE t.FAC_STL_GRD = :1 AND t.FAC_CODE = :2),
       0,1,0) CHECKRESULT
FROM DUAL                       
</query>
	
<query   id="UIB010050.FINDTAFACSTLGRD" desc="查询钢号对应的目标钢号">
		SELECT DISTINCT Z.TAFAC_STL_GRD 
		FROM TBB01_FAC_INGR X,TBB01_MAP_INGR Y,TBB01_TAFAC_INGR Z
		WHERE X.FAC_STL_GRD = Y.FAC_STL_GRD 
		AND X.FAC_CODE = Y.FAC_CODE
		AND Y.TAFAC_STL_GRD = Z.TAFAC_STL_GRD
		AND X.FAC_STL_GRD = :1
		AND X.FAC_CODE    = :2             
</query>
	
<query   id="UIB010050.UPDATE.MAPINGR" desc="更新MAPINGR表中的厂内钢号">
		UPDATE TBB01_MAP_INGR T
		SET  T.FAC_STL_GRD  = :1,
			 T.FAC_CODE     = :2
		WHERE T.FAC_STL_GRD = :3
		AND   T.FAC_CODE    = :4              
</query>		
	
<query id="UIB010050_03.UPDATE" desc="备注">
	    UPDATE       TBB01_FAC_INGR   T
        SET          T.FAC_DETAIL     =     ?
        WHERE        T.FAC_STL_GRD    =     ?
        AND          T.FAC_CODE       =     ? 
</query>	
</queryMap>