SELECT F.CAL_NO ROLL_MANA_NO, F.CAL_COIL_SEQ ROLL_COIL_SEQ, F.C_COIL_NO COIL_NO, F.L_COIL_NO C_COIL_NO, A.H_COIL_NO1, A1.CJ_REMARK, A1.SPM_RMK, A1.DEAL_REMARK, A1.CPCJ_REMARK, A.COIL_THK, A.COIL_WTH, A.COIL_LEN, A.COIL_INDIA, A.COIL_OUTDIA, A.ACT_WGT, Q.C_ORD_INDIA INDIA,--冷轧订单内径 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, 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.MILL_DTIME,'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.L_COIL_ID 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 ,decode(F.CAL_TYPE,'1','正常退火','2','返修','3','过渡') CAL_TYPE ,(nvl(F.CAL_ID,0)+1) CAL_CNT ,XB_PACKAGE.GF_COMNNAME_FIND('B02001' , A.CRK_CD1)||CASE WHEN A.CRK_CD2 IS NOT NULL THEN '+' ELSE ' ' END ||XB_PACKAGE.GF_COMNNAME_FIND('B02001' , A.CRK_CD2) COIL_CRK ,(SELECT CC.CAL_TYPE_TEMP FROM C_TBC02_COIL_COMM CC WHERE CC.OLD_SAMPL_NO = F.L_COIL_NO AND ROWNUM = 1 ) CAL_TYPE_TEMP ,C.MK_CONTENT --20121205 ,LWC.VALUE_C ,LWC.VALUE_Mn ,LWC.VALUE_Si ,LWC.VALUE_Ti ,A.L3_ROUGH ,Q.OIL_DEF ,F.CAL_MEMO FROM C_TBL02_COIL_COMM A, tbh02_coil_comm A1 , TBE02_ORD_PRC C, TBZ00_COMMCD B, TBZ00_CUSTOMER D, TBZ00_COMMCD E, L_TBF03_SPEC_MILL F, L_TBF03_SPEC_CAL G, TBZ00_COMMCD H, TBZ00_COMMCD I, tba01_ord_line Q ,(select FF.C_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 from C_TBL02_COIL_COMM AC, TBB02_WORK_INGR WI,TBG02_CHARGE_COMM_D CD, TBE02_ORD_PRC CC, L_TBF03_SPEC_MILL FF, L_TBF03_SPEC_CAL GG where FF.ORD_NO = CC.ORD_NO AND FF.ORD_SEQ = CC.ORD_SEQ AND FF.CAL_NO = GG.CAL_MANA_NO AND AC.COIL_NO = FF.C_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') GROUP BY FF.C_COIL_NO) LWC WHERE F.ORD_NO=C.ORD_NO AND F.ORD_SEQ=C.ORD_SEQ AND F.ORD_NO = Q.ORD_NO AND F.ORD_SEQ = Q.ORD_SEQ AND A.CUR_PROG_CD=B.SM_CD(+) AND C.ORDCUST_CD=D.CUST_CD AND F.CAL_NO=G.CAL_MANA_NO AND A.COIL_NO = LWC.COIL_NO_LWC(+) AND A.H_COIL_NO1 = A1.OLD_SAMPL_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.C_COIL_NO AND G.STATUS_CD <> 'D' AND F.STATUS_CD IN('A','B','C','D','F') ORDER BY F.CAL_NO,F.CAL_COIL_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