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 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 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 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 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 SELECT * FROM TBB01_CUST_INGR T WHERE T.CUST_SPEC_NO = ? AND T.FAC_TP = ? AND T.CHEM_CD = ? 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 = ? 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 = ? 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')) DELETE FROM TBB01_CUST_INGR WHERE CUST_SPEC_NO = ? AND FAC_TP = ? DELETE FROM TBB01_CUST_INGR WHERE CUST_SPEC_NO = ? AND FAC_TP = ? AND CHEM_CD = ? {call ZL_NORM_MANAGE.COPY_CUST_CHEM(?,?,?,?,?)}