SELECT QLTY_CD_CFNM LABEL , QLTY_CD VALUE FROM TBB01_SEQ_QLTY SELECT DISTINCT substr(A.SMP_NO , 1 , 10) LABEL , substr(A.SMP_NO , 1 , 10) VALUE FROM TBB02_LOT_COIL A , TBB02_LOT_COM B , TBH02_COIL_COMM C WHERE A.COIL_NO = C.COIL_NO AND A.SMP_NO = B.SMP_NO AND A.SMP_CUT_LOC = B.SMP_CUT_LOC AND C.MATLQLTY_DEC_GRD = '2' AND C.TOT_DEC_GRD IS NULL SELECT DISTINCT substr(A.SMP_NO , 11) LABEL , substr(A.SMP_NO , 11) VALUE FROM TBB02_LOT_COIL A , TBB02_LOT_COM B , TBH02_COIL_COMM C WHERE A.COIL_NO = C.COIL_NO AND A.SMP_NO = B.SMP_NO AND A.SMP_CUT_LOC = B.SMP_CUT_LOC AND C.MATLQLTY_DEC_GRD = '2' AND C.TOT_DEC_GRD IS NULL AND substr(A.SMP_NO , 1 , 10) = ? SELECT 'N' CHK , A.COIL_NO --钢卷号 , A.SMP_TIT_YN --是否取样 , A.SMP_NO --试样号 , A.SMP_CUT_LOC --取样位置 , A.ORD_NO||A.ORD_SEQ ORD ---订单号 , ( SELECT SMP_LOT_CD||SMP_LEN_LOC||SMP_WTH_LOC FROM TBB02_INS_QLTY WHERE SMP_NO = B.SMP_NO AND SMP_CUT_LOC = B.SMP_CUT_LOC AND QLTY_CD = 'SAMP' ) SAMPLINGCODE --取样代码 , B.EXAMORG_CD --检验机关 , B.SPEC_ABBSYM --标准号 , B.SPEC_STL_GRD --标准钢号 , B.ORD_USE_TP --订单用途 , A.COIL_THK --钢卷厚度 , A.QLTY_MAN_DEC_GRD --材质结果 , B.QLTY_MAN_INS_TP --是否为手工判定 , C.MATLQLTY_DEC_GRD FROM TBB02_LOT_COIL A , TBB02_LOT_COM B , TBH02_COIL_COMM C WHERE A.COIL_NO = C.COIL_NO AND A.SMP_NO = B.SMP_NO AND A.SMP_CUT_LOC = B.SMP_CUT_LOC AND C.TOT_DEC_GRD IS NULL ---------------------------------------------------------------------------- --取样位置非W的情况下 ---------------------------------------------------------------------------- SELECT 'N' CHK , X.* , Y.QLTY_DCS_RST , Y.COIL_NO COIL_NO1 , decode(Y.QLTY_CD , null , 'Y') EX FROM (SELECT A.SMP_NO SMP_NO1 , A.SMP_CUT_LOC SMP_CUT_LOC1 , A.QLTY_CD , to_number('') QLTY_SEQ --实验次数 , A.QLTY_CD QLTY_NM , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_UNIT , B.QLTY_VAL_WK QLTY_VAL_WK1 --实绩值 , B.QLTY_UNIT_WK QLTY_UNIT_WK1 --类型 FROM TBB02_INS_QLTY A , TBB02_WORK_QLTY B WHERE (A.SMP_NO = B.SMP_NO AND A.SMP_CUT_LOC = B.SMP_CUT_LOC AND A.QLTY_CD = B.QLTY_CD) AND A.SMP_NO = ? AND A.SMP_CUT_LOC = ? ) X LEFT JOIN TBB02_DCS_QLTY Y --可能还未判定，所以这里需要使用左连接 ON X.QLTY_CD = Y.QLTY_CD AND Y.COIL_NO = ? WHERE X.QLTY_CD != 'SAMP' UNION SELECT 'N' CHK , X.* , Y.QLTY_DCS_RST , Y.COIL_NO COIL_NO1 , decode(Y.QLTY_CD , null , 'Y') EX FROM (SELECT A.SMP_NO SMP_NO1 , A.SMP_CUT_LOC SMP_CUT_LOC1 , A.QLTY_CD , B.QLTY_SEQ --实验次数 , A.QLTY_CD QLTY_NM , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_UNIT , B.QLTY_VAL_WK QLTY_VAL_WK1 --实绩值 , B.QLTY_UNIT_WK QLTY_UNIT_WK1 --类型 FROM TBB02_INS_QLTY A , TBB02_REWORK_QLTY B WHERE (A.SMP_NO = B.SMP_NO AND A.SMP_CUT_LOC = B.SMP_CUT_LOC AND A.QLTY_CD = B.QLTY_CD) AND A.SMP_NO = ? AND A.SMP_CUT_LOC = ? ) X LEFT JOIN TBB02_REDCS_QLTY Y --可能还未判定，所以这里需要使用左连接 ON X.QLTY_CD = Y.QLTY_CD AND X.QLTY_SEQ = Y.QLTY_SEQ AND Y.COIL_NO = ? WHERE X.QLTY_CD != 'SAMP' ------------------------------------------------------------------------- --取样位置为W时 ------------------------------------------------------------------------- SELECT 'N' CHK , X.* , Y.QLTY_DCS_RST , Y.COIL_NO COIL_NO1 , decode(Y.QLTY_CD , null , 'Y') EX FROM (SELECT A.SMP_NO SMP_NO1 , A.SMP_CUT_LOC SMP_CUT_LOC1 , A.QLTY_CD , to_number('') QLTY_SEQ --实验次数 , A.QLTY_CD QLTY_NM , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_TYPE , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_VAL_WK)) QLTY_VAL_WK1 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_UNIT_WK)) QLTY_UNIT_WK1 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_VAL_WK)) QLTY_VAL_WK2 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_UNIT_WK)) QLTY_UNIT_WK2 FROM TBB02_INS_QLTY A , TBB02_WORK_QLTY B WHERE (A.SMP_NO = B.SMP_NO AND A.QLTY_CD = B.QLTY_CD) AND A.SMP_NO = ? GROUP BY A.SMP_NO , A.SMP_CUT_LOC , A.QLTY_CD , A.QLTY_CD , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_TYPE ) X LEFT JOIN TBB02_DCS_QLTY Y ON X.QLTY_CD = Y.QLTY_CD AND Y.COIL_NO = ? WHERE X.QLTY_CD != 'SAMP' UNION SELECT 'N' CHK , X.* , Y.QLTY_DCS_RST , Y.COIL_NO COIL_NO1 , decode(Y.QLTY_CD , null , 'Y') EX FROM (SELECT A.SMP_NO SMP_NO1 , A.SMP_CUT_LOC SMP_CUT_LOC1 , A.QLTY_CD , B.QLTY_SEQ --实验次数 , A.QLTY_CD QLTY_NM , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_TYPE , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_VAL_WK)) QLTY_VAL_WK1 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W1' , QLTY_UNIT_WK)) QLTY_UNIT_WK1 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_VAL_WK)) QLTY_VAL_WK2 , max(decode(A.SMP_CUT_LOC||B.SMP_CUT_LOC , 'W2' , QLTY_UNIT_WK)) QLTY_UNIT_WK2 FROM TBB02_INS_QLTY A , TBB02_REWORK_QLTY B WHERE (A.SMP_NO = B.SMP_NO AND A.QLTY_CD = B.QLTY_CD) AND A.SMP_NO = ? GROUP BY A.SMP_NO , A.SMP_CUT_LOC , A.QLTY_CD , B.QLTY_SEQ , A.QLTY_CD , A.QLTY_MIN , A.QLTY_MAX , A.QLTY_TYPE ) X LEFT JOIN TBB02_REDCS_QLTY Y ON X.QLTY_CD = Y.QLTY_CD AND X.QLTY_SEQ = Y.QLTY_SEQ AND Y.COIL_NO = ? WHERE X.QLTY_CD != 'SAMP' UPDATE TBB02_LOT_COIL SET QLTY_MAN_DEC_GRD = ? WHERE COIL_NO = ? UPDATE TBB02_LOT_COM SET SMP_PROG_CD = 'D' , MOD_ID = ? , MOD_DTIME = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MIHH') WHERE SMP_NO = ? AND SMP_CUT_LOC = ? UPDATE TBB02_LOT_COM SET QLTY_MAN_INS_TP = ? --手工判定 , MOD_ID = ? --修改人 , MOD_DTIME = TO_CHAR(SYSDATE , 'YYYYMMDDHH24MIHH') --修改时间 WHERE SMP_NO = ? AND SMP_CUT_LOC = ? UPDATE TBB02_WORK_QLTY SET QLTY_VAL_WK = ? ,QLTY_UNIT_WK = ? ,QLTY_WK_TP = 'M' --'M'表示被修改了 WHERE SMP_NO = ? AND SMP_CUT_LOC = ? AND QLTY_CD = ? UPDATE TBB02_REWORK_QLTY SET QLTY_VAL_WK = ? ,QLTY_UNIT_WK = ? WHERE SMP_NO = ? AND SMP_CUT_LOC = ? AND QLTY_CD = ? AND QLTY_SEQ = ? UPDATE TBB02_REDCS_QLTY SET QLTY_DCS_RST = ? WHERE COIL_NO = ? AND QLTY_CD = ? AND QLTY_SEQ = ? UPDATE TBB02_DCS_QLTY SET QLTY_DCS_RST = ? WHERE COIL_NO = ? AND QLTY_CD = ? DELETE FROM TBB02_REWORK_QLTY WHERE SMP_NO = ? AND SMP_CUT_LOC = ? AND QLTY_CD = ? AND QLTY_SEQ = ? DELETE FROM TBB02_REDCS_QLTY WHERE QLTY_DCS_RST = ? AND COIL_NO = ? AND QLTY_CD = ? AND QLTY_SEQ = ? INSERT INTO TBB02_REWORK_QLTY( QLTY_CD , QLTY_SEQ , QLTY_VAL_WK , QLTY_UNIT_WK , SMP_NO , SMP_CUT_LOC ) VALUES(?,?,?,?,?,?) INSERT INTO TBB02_REDCS_QLTY( COIL_NO , QLTY_CD , QLTY_SEQ , QLTY_DCS_RST )VALUES(?,?,?,?) INSERT INTO TBB02_DCS_QLTY(COIL_NO , QLTY_CD , QLTY_DCS_RST) VALUES(?,?,?) {call NIB020161(?,?,?,?)}