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
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
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
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
SELECT T.FAC_DETAIL
FROM TBB01_FAC_INGR T
WHERE T.FAC_STL_GRD = ?
AND T.FAC_CODE = ?
AND T.CHEM_CD = 'C'
UPDATE TBB01_FAC_INGR SET
FAC_STL_GRD = ? --更新后的厂内钢号信息
,FAC_CODE = ? --更新后的生产线
WHERE FAC_STL_GRD = ? -- 更新前的厂内钢号信息
AND FAC_CODE = ? --生产线
UPDATE TBB01_FAC_INGR SET
CHEM_AIM = ? --成分目标值
, CHEM_MIN = ? --成分最小值
, CHEM_MAX = ? --成分最大值
WHERE FAC_STL_GRD = ? --厂内钢号
AND CHEM_CD = ? --成分
AND FAC_CODE = ? --生产线
DELETE FROM TBB01_FAC_INGR WHERE FAC_STL_GRD = ? AND FAC_CODE = ?
INSERT INTO TBB01_FAC_INGR(
FAC_STL_GRD --厂内钢号
,FAC_CODE --生产线
,CHEM_CD --成分
,COMP_YN --复合元素区分
,CHEM_AIM --成分目标值
,CHEM_MIN --成分最小值
,CHEM_MAX --成分最大值
--,FAC_TP --生产线
) VALUES(?,?,?,?,?,?,?)
{call NIB015051(?,?,?,?)}
DELETE FROM TBB01_FAC_INGR WHERE FAC_STL_GRD = ? AND FAC_CODE = ? AND CHEM_CD = ?
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
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
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
UPDATE TBB01_FAC_INGR T
SET T.FAC_DETAIL = ?
WHERE T.FAC_STL_GRD = ?
AND T.FAC_CODE = ?