XinYu_RZMES/.svn/pristine/b5/b512ee07482c435ae808ed3dfd629565ecab4009.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="客户成分参数录入">
		
	<query id="UIB010030_01.SELECT" desc="按客户公司代码或客户公司名称查询">
		 SELECT 
		 DISTINCT 
		 SUBSTR(CUST_SPEC_NO , 1 , 6)   CUST_CD      --客户公司代码
        ,SUBSTR(CUST_SPEC_NO , 7 , 2)   PRDNM_CD     --品名
        ,SUBSTR(CUST_SPEC_NO , 9 , 2)   SEQ          --序号
		,SUBSTR(CUST_SPEC_NO , 11)      PROD_LINE    --产线区分
		,SPEC_STL_GRD
		,PKG_QUALITY_COMM.FIB020202(SUBSTR(CUST_SPEC_NO , 1 , 6))   CUST_NM
         FROM      TBB01_CUST_INGR
         WHERE     CUST_SPEC_NO         LIKE     ?||'%'
		 AND       FAC_TP               =        ?
		 ORDER BY  CUST_CD , PRDNM_CD , SEQ
	</query>

	<query id="UIB010030_02.SELECT" desc="成分代码及最大，最小值查询，不包含复合成分及CEQ,PCM表达式">
		SELECT  
		A.CHEM_CD  
	   ,B.CHEM_MAX 
	   ,B.CHEM_MIN 
	   ,B.CHEM_DEC_CD	
       ,''  COMP_YN
        FROM        TBB01_SEQ_INGR A , TBB01_CUST_INGR B 
        WHERE       A.CHEM_CD     =   B.CHEM_CD(+) 
        AND         B.CUST_SPEC_NO(+)    =     NVL(?||?||?||? , 'X')
		AND         B.FAC_TP(+)                       =   ?
        AND         A.COMP_YN                         =   'N'
        ORDER BY    DECODE(B.CHEM_MAX || B.CHEM_MIN, NULL, 1, 0), A.DISPLAY_SEQ
	</query>
	
	<query id="UIB010030_03.SELECT" desc="复合成分代码及最大，最小值查询、ceq，pcm表达式及最大最小值">
		 SELECT  
		 A.CHEM_CD  
	    ,B.CHEM_MAX 
	    ,B.CHEM_MIN 
		,B.CHEM_DEC_CD
        ,A.COMP_DETAIL  
        ,A.COMP_CAL
        ,A.COMP_YN
         FROM  TBB01_SEQ_INGR A , TBB01_CUST_INGR B 
         WHERE       A.CHEM_CD          			  =      B.CHEM_CD(+) 
         AND         CUST_SPEC_NO(+)    			  =      NVL(?||?||?||? , 'X')
		 AND         B.FAC_TP(+)                      =      ?
         AND         A.COMP_YN                        =     'Y'
         ORDER BY    DECODE(B.CHEM_MAX || B.CHEM_MIN, NULL, 1, 0), A.DISPLAY_SEQ
	</query>
	
	<query id="UIB010030_04.SELECT" desc="新增时获得成分代码，不包含复合成分及CEQ,PCM表达式">
		 SELECT 
		 CHEM_CD      --成分
		,''  CHEM_MIN
		,''  CHEM_MAX
		,''  COMP_YN
		,''  CHEM_DEC_CD
		,DISPLAY_SEQ  --元素显示序号
		 FROM         TBB01_SEQ_INGR 
		 WHERE        COMP_YN = 'N' 
	     ORDER BY     DISPLAY_SEQ
	</query>
	<query id="UIB010030_05.SELECT" desc="复合成分代码查询、ceq，pcm表达式">
		 SELECT 
		 CHEM_CD         --成分
		,COMP_DETAIL  --详细内容
		,COMP_CAL     --复合内容
		,DISPLAY_SEQ  --元素显示序号
		,'' CHEM_MIN
		,'' CHEM_MAX
		,''  CHEM_DEC_CD
		,COMP_YN
		 FROM         TBB01_SEQ_INGR 
		 WHERE        COMP_YN      =    'Y' 
		 ORDER BY     DISPLAY_SEQ
	</query>
	<query id="UIB010030_06.SELECT" desc="查询该元素是否存在">
		SELECT * FROM TBB01_CUST_INGR  T WHERE T.CUST_SPEC_NO = ? AND T.FAC_TP = ? AND T.CHEM_CD = ?
	</query>
	
	<query id="UIB010030_01.UPDATE" desc="更新客户参数号">
		UPDATE     TBB01_CUST_INGR 
		SET        CUST_SPEC_NO  =  ?  --更新后的客户参数号
				  ,SPEC_STL_GRD  =  ? 	
		          ,MOD_ID        =  ?
		          ,MOD_DTIME     =  TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
        WHERE      CUST_SPEC_NO  =  ?    --更新前的客户参数号
		AND        FAC_TP        =  ?    
	</query>
	
	<query id="UIB010030_02.UPDATE" desc="更新成分">
		UPDATE     TBB01_CUST_INGR 
		SET        CHEM_MIN      =  ?    --成分最小值
                  ,CHEM_MAX      =  ?    --成分最大值
		          ,MOD_ID        =  ?
				  ,CHEM_DEC_CD	 =  ?    --检验类型				
		          ,MOD_DTIME     =  TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS')
        WHERE      CUST_SPEC_NO  =  ?    --更新后的客户参数号
		AND        CHEM_CD       =  ?    --成分(与客户参数号共同做为主键)
		AND        FAC_TP        =  ? 
	</query>
	
	<query id="UIB010030_01.INSERT" desc="插入新的元素成分">
		  INSERT INTO  TBB01_CUST_INGR(
		  CUST_SPEC_NO   --客户参数号
		, CHEM_CD        --成分(与客户参数号共同做为主键)
		, CHEM_MIN       --成分最小值
		, CHEM_MAX       --成分最大值
		, COMP_YN
		, FAC_TP
		, SPEC_STL_GRD
		, CHEM_DEC_CD   --检验类型
		, REG_ID       
		, REG_DTIME    
		) VALUES(? , ? , ? , ?  , ?  , ?, ? , ? , ? , TO_CHAR(SYSDATE , 'YYYYMMDDHH24MISS'))
	</query>
	
	<query id="UIB010030_01.DELETE" desc="删除某客户参数号">
		DELETE FROM TBB01_CUST_INGR WHERE CUST_SPEC_NO = ?  AND FAC_TP = ? 
	</query>
	
	<query id="UIB010030_02.DELETE" desc="删除某客户参数号的某元素">
		DELETE FROM TBB01_CUST_INGR WHERE CUST_SPEC_NO = ?    AND FAC_TP = ?  AND CHEM_CD = ? 
	</query>
	
	<query id="UIB010030_01.CALL" desc="客户成分拷贝">
		{call ZL_NORM_MANAGE.COPY_CUST_CHEM(?,?,?,?,?)}
	</query>
</queryMap>