SELECT QLTY_CD_CFNM LABEL , QLTY_CD VALUE FROM TBB01_SEQ_QLTY /*SELECT DISTINCT SUBSTR(SMP_NO , 1 , 10) LABEL , SUBSTR(SMP_NO , 1 , 10) VALUE FROM TBB02_LOT_COM WHERE SMP_PROG_CD = 'E' ORDER BY LABEL */ SELECT 'X' LABEL , 'X' VALUE FROM DUAL SELECT DISTINCT SUBSTR(a.SMP_NO ,11) LABEL , SUBSTR(a.SMP_NO ,11) VALUE FROM TBB02_LOT_COM a WHERE SUBSTR(a.SMP_NO ,1 , 10) = ? AND A.SMP_PROG_CD = 'E' SELECT CCM.COIL_NO ,CCM.OLD_SAMPL_NO ,LCM.SMP_NO ,LCM.SMP_CUT_LOC ,LCL.SMP_TIT_YN ,LCL.ORD_NO||LCL.ORD_SEQ ORD_NO ,LCM.SPEC_ABBSYM ,LCM.SPEC_STL_GRD ,LCL.COIL_THK ,CCM.MATLQLTY_DEC_GRD ,LCM.QLTY_DCS_DTIME ,XB_PACKAGE.GF_USER_FIND(CCM.MATLQLTY_DEC_EMP_ID) MATLQLTY_DEC_EMP_ID ,'N' OVER_DCS ,DECODE(CCM.TOT_DEC_GRD , NULL, '' , 'Y') TOT_OVER FROM TBH02_COIL_COMM CCM , TBB02_LOT_COM LCM , TBB02_LOT_COIL LCL WHERE LCM.SMP_PROG_CD = 'E' AND LCL.COIL_NO = CCM.COIL_NO AND LCM.SMP_NO = LCL.SMP_NO ------------------------------------------------------------ --位置SMP_CUT_LOC为非W的情况；对于DSN_KIND ， 客户是1，标准是2 ------------------------------------------------------------ SELECT A.QLTY_CD , to_number('') QLTY_SEQ , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null)) CUST_QLTY_MIN --客户材质 , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null)) CUST_QLTY_MAX , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null)) SPEC_QLTY_MIN --标准材质 , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null)) SPEC_QLTY_MAX , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT , B.QLTY_VAL_WK QLTY_VAL_WK1 --材质实绩值 , B.QLTY_UNIT_WK QLTY_UNIT_WK1 --材质TYPE , ( SELECT QLTY_DCS_RST FROM TBB02_DCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD ) QLTY_DCS_RST FROM TBB01_ORD_QLTY A , TBB02_WORK_QLTY B WHERE A.QLTY_CD = B.QLTY_CD AND B.SMP_NO = ? AND B.SMP_CUT_LOC = ? AND ORD_NO||ORD_SEQ = ? AND A.QLTY_CD != 'SAMP' GROUP BY A.QLTY_CD , B.QLTY_VAL_WK , B.QLTY_UNIT_WK ------------------------------ UNION ALL ------------------------------- SELECT A.QLTY_CD , B.QLTY_SEQ , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null)) CUST_QLTY_MIN --客户材质 , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null)) CUST_QLTY_MAX , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null)) SPEC_QLTY_MIN --标准材质 , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null)) SPEC_QLTY_MAX , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT , B.QLTY_VAL_WK QLTY_VAL_WK1 --材质实绩值 , B.QLTY_UNIT_WK QLTY_UNIT_WK1 --材质单位 , ( SELECT QLTY_DCS_RST FROM TBB02_REDCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD AND QLTY_SEQ = B.QLTY_SEQ ) QLTY_DCS_RST FROM TBB01_ORD_QLTY A , TBB02_REWORK_QLTY B WHERE A.QLTY_CD = B.QLTY_CD AND B.SMP_NO = ? AND B.SMP_CUT_LOC = ? AND ORD_NO||ORD_SEQ = ? AND A.QLTY_CD != 'SAMP' GROUP BY A.QLTY_CD , B.QLTY_SEQ , B.QLTY_VAL_WK , B.QLTY_UNIT_WK ------------------------------------------------------------ --位置SMP_CUT_LOC为为W的情况，1为头，2为尾；对于DSN_KIND ， 客户是1，标准是2 ------------------------------------------------------------ SELECT A.QLTY_CD , to_number('') QLTY_SEQ , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null)) CUST_QLTY_MIN --客户材质 , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null)) CUST_QLTY_MAX , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null)) SPEC_QLTY_MIN --标准材质 , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null)) SPEC_QLTY_MAX , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT , max(decode(B.SMP_CUT_LOC , '1' , QLTY_VAL_WK)) QLTY_VAL_WK1 --材质实绩值 , max(decode(B.SMP_CUT_LOC , '1' , QLTY_UNIT_WK)) QLTY_UNIT_WK1 --材质单位 , max(decode(B.SMP_CUT_LOC , '2' , QLTY_VAL_WK)) QLTY_VAL_WK2 --材质实绩值 , max(decode(B.SMP_CUT_LOC , '2' , QLTY_UNIT_WK)) QLTY_UNIT_WK2 --材质单位 , ( SELECT QLTY_DCS_RST FROM TBB02_DCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD ) QLTY_DCS_RST FROM TBB01_ORD_QLTY A , TBB02_WORK_QLTY B WHERE A.QLTY_CD = B.QLTY_CD AND B.SMP_NO = ? AND ORD_NO||ORD_SEQ = ? AND A.QLTY_CD != 'SAMP' GROUP BY A.QLTY_CD ------------------------------ UNION ALL ------------------------------- SELECT A.QLTY_CD , B.QLTY_SEQ , max(decode(A.DSN_KIND , '1' ,A.QLTY_MIN , null)) CUST_QLTY_MIN --客户材质 , max(decode(A.DSN_KIND , '1' ,A.QLTY_MAX , null)) CUST_QLTY_MAX , max(decode(A.DSN_KIND , '1' ,A.QLTY_UNIT , null)) CUST_QLTY_UNIT , max(decode(A.DSN_KIND , '2' ,A.QLTY_MIN , null)) SPEC_QLTY_MIN --标准材质 , max(decode(A.DSN_KIND , '2' ,A.QLTY_MAX , null)) SPEC_QLTY_MAX , max(decode(A.DSN_KIND , '2' ,A.QLTY_UNIT , null)) SPEC_QLTY_UNIT , max(decode(B.SMP_CUT_LOC , '1' , QLTY_VAL_WK)) QLTY_VAL_WK --材质实绩值，1表头 , max(decode(B.SMP_CUT_LOC , '1' , QLTY_UNIT_WK)) QLTY_TYPE_WK --材质类型，1表头 , max(decode(B.SMP_CUT_LOC , '2' , QLTY_VAL_WK)) QLTY_VAL_WK1 --材质实绩值，2表尾 , max(decode(B.SMP_CUT_LOC , '2' , QLTY_UNIT_WK)) QLTY_TYPE_WK1 --材质类型，2表尾 , ( SELECT QLTY_DCS_RST FROM TBB02_REDCS_QLTY WHERE COIL_NO = ? AND QLTY_CD = A.QLTY_CD AND QLTY_SEQ = B.QLTY_SEQ ) QLTY_DCS_RST FROM TBB01_ORD_QLTY A , TBB02_REWORK_QLTY B WHERE A.QLTY_CD = B.QLTY_CD AND B.SMP_NO = ? AND ORD_NO||ORD_SEQ = ? AND A.QLTY_CD != 'SAMP' GROUP BY A.QLTY_CD , B.QLTY_SEQ UPDATE TBB02_LOT_COM T SET T.SMP_PROG_CD = 'D' ,T.QLTY_DCS_TP = '' ,T.QLTY_DCS_DTIME = '' WHERE T.SMP_NO = ? UPDATE TBH02_COIL_COMM SET MATLQLTY_DEC_GRD = '' ,MATLQLTY_DEC_EMP_ID = '' WHERE SAMPL_NO = ?