SELECT F.ROLL_MANA_NO, F.ROLL_COIL_SEQ, XB_PACKAGE.GF_COMNNAME_FIND('A01004', C.PRD_TY) CPLX, F.COIL_NO, F.C_COIL_NO, A.COIL_THK, A.COIL_WTH, A.COIL_LEN, A.LZREMARK, A.COIL_INDIA, A.COIL_OUTDIA, A.ACT_WGT, R.SPM_RMK, --质检备注 R.CJ_REMARK, --热轧车间备注 R.CPCJ_REMARK, --成品车间备注 R.DEAL_REMARK, --处置意见 F.MILL_MEMO, C.C_ORD_THK, --C.C_ORD_THK_MIN, --C.C_ORD_THK_MAX, C.C_ORD_WTH, --C.C_ORD_WTH_MIN, --C.C_ORD_WTH_MAX, (DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN)+ DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX))/2 M_THK , DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN) C_ORD_THK_MIN, DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX) C_ORD_THK_MAX, DECODE(C.SUAN_WTH_MIN, NULL,C.C_ORD_WTH_MIN,C.SUAN_WTH_MIN) C_ORD_WTH_MIN, DECODE(C.SUAN_WTH_MAX,NULL, C.C_ORD_WTH_MAX,C.SUAN_WTH_MAX) C_ORD_WTH_MAX, C.C_ORD_LEN, C.C_ORD_INDIA, C.C_ORD_OUTDIA, C.C_EXTSHAPE_REQ, B.SM_CSNM CUR_PROG_CD, F.STATUS_CD, A.CUR_LOAD_LOC, C.ACID_PROD_PATH, C.ORD_NO, C.ORD_SEQ, C.C_SPEC_STL_GRD, C.C_SPEC_ABBSYM, H.SM_CFNM ORD_USEAGE_CD, C.DEL_TO_DATE, D.CUST_NM ORDCUST_CD, E.SM_CFNM DEST_CD, ROUND(SYSDATE-TO_DATE(A.RECEIVE_TIME,'YYYY-MM-DD HH24:MI:SS'),1) COLDTIM, C.ORD_UNIT_WGT, C.ORD_UNIT_WGT_MIN, C.ORD_UNIT_WGT_MAX, C.C_PRODNM_CD, F.C_COIL_ID, I.SM_CFNM PACK_METHOD, DECODE(C.EDGE_PROC_YN,'Y','√',NULL) EDGE_PROC_YN, --(C.C_ORD_THK_MIN+C.C_ORD_THK_MAX)/2 M_THK , XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD1)||CASE WHEN A.CRK_CD2 IS NOT NULL THEN '+' ELSE ' ' END ||XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD2) COIL_CRK --,C.MK_CONTENT --20121205 ,L.C_ORD_INDIA JQU --卷曲内经 ,L.HARSHNESS --粗燥度 ,L.APPLICATION --用途 ,LWC.VALUE_C ,LWC.VALUE_Mn ,LWC.VALUE_Si ,LWC.VALUE_Ti ,LWC.VALUE_S ,LWC.VALUE_P FROM C_TBK02_COIL_COMM A, TBE02_ORD_PRC C, TBZ00_COMMCD B, TBZ00_CUSTOMER D, TBZ00_COMMCD E, C_TBF03_SPEC_MILL F, C_TBF03_SPEC_ROLL G, TBZ00_COMMCD H, TBZ00_COMMCD I, TBA01_ORD_LINE L , TBH02_COIL_COMM R, (select FF.COIL_NO COIL_NO_LWC, max(DECODE(WI.CHEM_CD, 'C', WI.CHEM_VAL)) VALUE_C, max(DECODE(WI.CHEM_CD, 'Mn', WI.CHEM_VAL)) VALUE_Mn, max(DECODE(WI.CHEM_CD, 'Si', WI.CHEM_VAL)) VALUE_Si, max(DECODE(WI.CHEM_CD, 'Ti', WI.CHEM_VAL)) VALUE_Ti, max(DECODE(WI.CHEM_CD, 'S', WI.CHEM_VAL)) VALUE_S, max(DECODE(WI.CHEM_CD, 'P', WI.CHEM_VAL)) VALUE_P from C_TBK02_COIL_COMM AC, TBB02_WORK_INGR WI, TBG02_CHARGE_COMM_D CD, TBE02_ORD_PRC CC, C_TBF03_SPEC_MILL FF, C_TBF03_SPEC_ROLL GG where FF.ORD_NO = CC.ORD_NO AND FF.ORD_SEQ = CC.ORD_SEQ AND FF.ROLL_MANA_NO = GG.ROLL_MANA_NO AND AC.COIL_NO = FF.COIL_NO AND GG.STATUS_CD != 'D' AND FF.STATUS_CD IN ('A', 'B', 'C', 'D', 'F') AND WI.CHARGE_NO = CD.CHARGE_NO AND WI.PROC_CD || WI.CHEM_SEQ = CD.LST_INGR_CD AND SUBSTR(AC.SLAB_NO, 1, 10) = WI.CHARGE_NO AND WI.CHEM_CD IN ('C', 'Mn', 'Si', 'Ti','S','P') GROUP BY FF.COIL_NO) LWC WHERE F.ORD_NO=C.ORD_NO AND F.ORD_SEQ=C.ORD_SEQ AND F.ORD_NO=L.ORD_NO AND F.ORD_SEQ=L.ORD_SEQ AND A.CUR_PROG_CD=B.SM_CD AND A.COIL_NO=LWC.COIL_NO_LWC(+) AND C.ORDCUST_CD=D.CUST_CD AND F.ROLL_MANA_NO=G.ROLL_MANA_NO AND I.LG_CD='A01010' AND C.PACK_METHOD=I.SM_CD(+) AND C.C_ORD_USEAGE_CD=H.SM_CD(+) AND C.DEST_CD=E.SM_CD(+) AND A.COIL_NO=F.COIL_NO AND G.STATUS_CD<>'D' AND F.STATUS_CD IN('A','B','C','D','F') AND A.OLD_SAMPL_NO=R.OLD_SAMPL_NO(+) ORDER BY F.COIL_EDT_SEQ SELECT ROLL_MANA_NO,SUM(1) COIL_NUM FROM C_TBF03_SPEC_MILL WHERE STATUS_CD IN('A','B','C') GROUP BY ROLL_MANA_NO ORDER BY ROLL_MANA_NO SELECT F.ROLL_MANA_NO, F.ROLL_COIL_SEQ, F.COIL_NO, F.C_COIL_NO, A.COIL_THK, A.COIL_WTH, A.COIL_LEN, A.COIL_INDIA, A.COIL_OUTDIA, A.ACT_WGT, C.C_ORD_THK, --C.C_ORD_THK_MIN, --C.C_ORD_THK_MAX, C.C_ORD_WTH, --C.C_ORD_WTH_MIN, --C.C_ORD_WTH_MAX, (DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN)+ DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX))/2 M_THK , DECODE(C.SUAN_THK_MIN,NULL, C.C_ORD_THK_MIN, C.SUAN_THK_MIN) C_ORD_THK_MIN, DECODE(C.SUAN_THK_MAX,NULL, C.C_ORD_THK_MAX,C.SUAN_THK_MAX) C_ORD_THK_MAX, DECODE(C.SUAN_WTH_MIN, NULL,C.C_ORD_WTH_MIN,C.SUAN_WTH_MIN) C_ORD_WTH_MIN, DECODE(C.SUAN_WTH_MAX,NULL, C.C_ORD_WTH_MAX,C.SUAN_WTH_MAX) C_ORD_WTH_MAX, C.C_ORD_LEN, C.C_ORD_INDIA, C.C_ORD_OUTDIA, C.C_EXTSHAPE_REQ, B.SM_CSNM CUR_PROG_CD, F.STATUS_CD, A.CUR_LOAD_LOC, C.ACID_PROD_PATH, C.ORD_NO, C.ORD_SEQ, C.C_SPEC_STL_GRD, C.C_SPEC_ABBSYM, H.SM_CFNM ORD_USEAGE_CD, C.DEL_TO_DATE, D.CUST_NM ORDCUST_CD, E.SM_CFNM DEST_CD, ROUND(SYSDATE-TO_DATE(A.RECEIVE_TIME,'YYYY-MM-DD HH24:MI:SS'),1) COLDTIM, C.ORD_UNIT_WGT, C.ORD_UNIT_WGT_MIN, C.ORD_UNIT_WGT_MAX, C.C_PRODNM_CD, F.C_COIL_ID, I.SM_CFNM PACK_METHOD, DECODE(C.EDGE_PROC_YN,'Y','√',NULL) EDGE_PROC_YN, --(C.C_ORD_THK_MIN+C.C_ORD_THK_MAX)/2 M_THK , XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD1)||CASE WHEN A.CRK_CD2 IS NOT NULL THEN '+' ELSE ' ' END ||XB_PACKAGE.GF_COMNNAME_FIND('H02001' , A.CRK_CD2) COIL_CRK ,C.MK_CONTENT --20121205 ,L.C_ORD_INDIA JQU --卷曲内经 ,L.HARSHNESS --粗燥度 ,L.APPLICATION --用途 ,L.L_BMD GM --光面、麻面 ,L.OIL_DEF --涂油代码 ,PKG_QUALITY_COMM.FZ00_COMM('A01010' ,L.PAKMTH_TP ) BAO --包装方式 FROM C_TBK02_COIL_COMM A, TBE02_ORD_PRC C, TBZ00_COMMCD B, TBZ00_CUSTOMER D, TBZ00_COMMCD E, C_TBF03_SPEC_MILL F, C_TBF03_SPEC_ROLL G, TBZ00_COMMCD H, TBZ00_COMMCD I, TBA01_ORD_LINE L WHERE F.ORD_NO=C.ORD_NO AND F.ORD_SEQ=C.ORD_SEQ and F.ORD_NO=L.ORD_NO AND F.ORD_SEQ=L.ORD_SEQ AND A.CUR_PROG_CD=B.SM_CD AND C.ORDCUST_CD=D.CUST_CD AND F.ROLL_MANA_NO=G.ROLL_MANA_NO AND I.LG_CD='A01010' AND C.PACK_METHOD=I.SM_CD(+) AND C.C_ORD_USEAGE_CD=H.SM_CD(+) AND C.DEST_CD=E.SM_CD(+) AND A.COIL_NO=F.COIL_NO AND G.STATUS_CD<>'D' AND F.STATUS_CD IN('A','B','C','D','F') ORDER BY F.COIL_EDT_SEQ