= NVL(?, 0) AND SUBSTR(NVL(A.YARD_ENTRY_DTIME, 0), 1, 8) <= NVL(?, 99999999999999) AND A.SPEC_STL_GRD || '&' LIKE ? || '%' AND A.COIL_WTH >= NVL(?, '00000') AND A.COIL_WTH <= NVL(?, '99999') AND A.COIL_THK >= NVL(?, '000') AND A.COIL_THK <= NVL(?, '999') AND A.CUR_LOAD_LOC || '&' LIKE ? || '%' AND A.OLD_SAMPL_NO LIKE ? || '%' AND A.ORD_NO || '&' LIKE ? || '%' AND C.ORD_DEVLMT_DATE || '&' LIKE ? || '%' AND A.CUR_PROG_CD || '&' LIKE ? || '%' AND E.SMP_SEND_DTIME || '&' LIKE ? || '%'--送样时间 AND a.SPM_YN || '&' like ? || '%'--平整 AND C.XSQY || '&' LIKE ? || '%'--销售区域 AND A.COIL_DEFECTIVE || '&' LIKE ? || '%'--次品区分 AND C.DLIV_TP || '&' LIKE ?|| '%' AND P.PROD_LINE || '&' LIKE ? || '%'--产线 AND P.RCVORD_CLF || '&' LIKE ? || '%' AND nvl(t.ROLL_MANA_NO, 'A0000') BETWEEN NVL(?, 'A0000') AND NVL(?, 'Z9999') ]]> 'Z') OR NVL(C.RZHQX,'T')='Z') then t.ACT_WGT / 1000 end) XCSL, sum(case when (((substr(t.ORD_NO, 1, 1) = '3' or substr(t.ORD_NO, 1, 2) = 'W3' or substr(t.ORD_NO, 1, 3) = 'WX3' or substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND d.ORDCUST_CD in ('224902', '243187') AND NVL(C.RZHQX,'T')<>'Z') OR NVL(C.RZHQX,'T')='Z') then t.ACT_WGT / 1000 end) XCZL, count(case when ((substr(t.ORD_NO, 1, 1) = '3' or substr(t.ORD_NO, 1, 2) = 'W3' or substr(t.ORD_NO, 1, 3) = 'WX3' or substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND (d.ORDCUST_CD in ('238948') OR instr(t.ORD_NO,'S')>0)) then t.ACT_WGT / 1000 end) TGSL, sum(case when ((substr(t.ORD_NO, 1, 1) = '3' or substr(t.ORD_NO, 1, 2) = 'W3' or substr(t.ORD_NO, 1, 3) = 'WX3' or substr(t.ORD_NO, 1, 6) = 'XGEC-3') AND (d.ORDCUST_CD in ('238948') OR instr(t.ORD_NO,'S')>0)) then t.ACT_WGT / 1000 end) TGZL, count(case when substr(t.ORD_NO, 1, 6) = 'XGEC-3' then t.ACT_WGT / 1000 end) CKSL, sum(case when substr(t.ORD_NO, 1, 6) = 'XGEC-3' then t.ACT_WGT / 1000 end) CKZL, count(case when ((substr(t.ORD_NO, 1, 1) = '4' or substr(t.ORD_NO, 1, 2) = 'W4' or substr(t.ORD_NO, 1, 6) = 'XGEC-4') AND NVL(C.RZHQX,'T')<>'Z') then t.ACT_WGT / 1000 end) SZSL, sum(case when ((substr(t.ORD_NO, 1, 1) = '4' or substr(t.ORD_NO, 1, 2) = 'W4' or substr(t.ORD_NO, 1, 6) = 'XGEC-4') AND NVL(C.RZHQX,'T')<>'Z' )then t.ACT_WGT / 1000 end) SZZL, count(case when (substr(t.ORD_NO, 1, 1) = '5' or substr(t.ORD_NO, 1, 2) = 'W5' or substr(t.ORD_NO, 1, 6) = 'XGEC-5') then t.ACT_WGT / 1000 end) LTSL, sum(case when (substr(t.ORD_NO, 1, 1) = '5' or substr(t.ORD_NO, 1, 2) = 'W5' or substr(t.ORD_NO, 1, 6) = 'XGEC-5') then t.ACT_WGT / 1000 end) LTZL FROM TBH02_COIL_COMM T, TBG02_SLAB_COMM B, TBA01_ORD_LINE C, TBA01_ORD_COMM D, TBB02_LOT_COM E, TBJ01_DLIV_DIR F, TBE02_ORD_PRC P, TBF02_SPEC_MILL A WHERE '1' = '1' AND T.SLAB_NO = B.SLAB_NO(+) AND T.ORD_NO = C.ORD_NO(+) AND T.ORD_SEQ = C.ORD_SEQ(+) AND T.ORD_NO = D.ORD_NO(+) AND T.SAMPL_NO = E.SMP_NO(+) AND T.COIL_NO = A.COIL_NO(+) AND T.DLIV_DIRNO = F.DLIV_DIRNO(+) AND T.ORD_NO = P.ORD_NO(+) AND T.ORD_SEQ = P.ORD_SEQ(+) AND T.COIL_STAT = '2' AND SUBSTR(NVL(T.YARD_ENTRY_DTIME, 99999999999999), 1, 8) >= NVL(?, 0) AND SUBSTR(NVL(T.YARD_ENTRY_DTIME, 0), 1, 8) <= NVL(?, 99999999999999) AND T.SPEC_STL_GRD || '&' LIKE ? || '%' AND T.COIL_WTH >= NVL(?, '00000') AND T.COIL_WTH <= NVL(?, '99999') AND T.COIL_THK >= NVL(?, '000') AND T.COIL_THK <= NVL(?, '999') AND T.CUR_LOAD_LOC || '&' LIKE ? || '%' AND T.OLD_SAMPL_NO LIKE ? || '%' AND T.ORD_NO || '&' LIKE ? || '%' AND C.ORD_DEVLMT_DATE || '&' LIKE ? || '%' AND T.CUR_PROG_CD || '&' LIKE ? || '%' AND e.SMP_SEND_DTIME || '&' LIKE ? || '%' --送样时间 AND t.SPM_YN || '&' like ? || '%' --平整 AND C.XSQY || '&' LIKE ? || '%' --销售区域 AND T.COIL_DEFECTIVE || '&' LIKE ? || '%' --次品区分 AND C.DLIV_TP || '&' LIKE ? || '%' AND P.PROD_LINE || '&' LIKE ? || '%' --产线 AND P.RCVORD_CLF || '&' LIKE ? || '%' AND nvl(A.ROLL_MANA_NO, 'A0000') BETWEEN NVL(?, 'A0000') AND NVL(?, 'Z9999') group by t.CUR_PROG_CD) select * from a union all select '合计', sum(a.hzSL), sum(a.hzzL), sum(a.RZHYSL), sum(a.RZHYzL), sum(a.RZqYSL), sum(a.RZqYzL), sum(a.XCSL), sum(a.XCzL), sum(a.TGSL), sum(a.TGzL), sum(a.CKSL), sum(a.CKzL), sum(a.SZSL), sum(a.SZzL), sum(a.LTSL), sum(a.LTzL) from a ]]> = nvl(?,'00000') AND A.SLAB_LEN <= nvl(?,'100000') AND A.ORD_NO || '&' LIKE ? || '%' AND A.ORD_FL || '&' LIKE ? || '%' AND A.SLAB_STOCK_TYPE || '&' like ? || '%' --热轧入库类型(1:热送入库 2 冷送入库 3 加回入库 4 轧回入库） AND A.SLAB_RT || '&' LIKE ? || '%' AND A.SLAB_INGR_DEC_GRD || '&' LIKE ? || '%' AND A.SLAB_WTH >= nvl(?,'0000') AND A.SLAB_WTH <= nvl(?,'100000') AND SUBSTR(nvl(C.CMT_DLIV_DEVLMT_DATE, 10000000), 1, 8) <= nvl(?, '50000000') --交货期 ------------------------------------------- AND C.DLIV_TP || '&' LIKE ? || '%' --运输方式 AND C.KEEPWARM_YN || '&' LIKE ? || '%' --------------------ADD BY WXY 091130 生产处要求 AND substr(A.SLAB_CUT_DTIME, 1, 8) >= ? AND substr(A.SLAB_CUT_DTIME, 1, 8) <= ? AND SUBSTR(A.SLAB_NO, 3, 1) || '&' LIKE ? || '%' AND SUBSTR(E.CC_DEVNO, 2, 1) || '&' LIKE ? || '%' AND C.XSQY || '&' LIKE ? || '%' AND A.SLAB_THK >= nvl(?,'0000') AND A.SLAB_THK <= nvl(?,'5000') ORDER BY STL_GRD, SLAB_NO, SLAB_WTH ]]> = ?) AND (SUBSTR(A.YARD_ENTRY_DTIME, 1, 8) <= ?) AND A.OLD_SAMPL_NO LIKE ? || '%' AND A.COIL_THK >= NVL(? , '000') AND A.COIL_THK <= NVL(? , '999') AND A.COIL_WTH >= NVL(? , '00000') AND A.COIL_WTH <= NVL(? , '99999') AND (A.CUR_LOAD_LOC LIKE ? || '%' OR A.CUR_LOAD_LOC IS NULL) AND A.SPEC_STL_GRD LIKE ? || '%' AND A.YARD_ENTRY_SHIFT || '&' LIKE ? || '%' AND A.YARD_ENTRY_GROUP || '&' LIKE ? || '%' AND A.YARD_ENTRY_REG || '&' LIKE ? || '%' AND A.TRANS_CAR_NO || '&' LIKE ? || '%' AND A.ORD_NO || '&' LIKE ? || '%' AND A.ORD_SEQ || '&' LIKE ? || '%' AND A.DLIV_DIRNO || '&' LIKE ? || '%' AND D.ORDCUST_CD || '&' LIKE ? || '%' AND A.DLIV_TP || '&' LIKE ? || '%' AND A.ORD_FL || '&' LIKE ? || '%' AND A.QK_YN || '&' LIKE ? || '%' AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.YARD_ENTRY_DTIME, 1, 8)) >= NVL(? , '0')) AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.YARD_ENTRY_DTIME, 1, 8)) <= NVL(? , '99999999')) AND A.COIL_IN_REG IS NULL -- ADD BY JUNGAN 2010-12-29 不查询盘入记录 order by a.OLD_SAMPL_NO desc ]]> = ?) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)>= :2)) AND (SUBSTR(A.TRNF_DTIME, 1, 8) <= ?) --or (a.QK_YN = 'Y' and SUBSTR(a.QK_REG_DTIME,1,8)<= :4)) and a.COIL_STAT = '3' AND nvl(a.ISDIVI, 1) <> '0' AND A.OLD_SAMPL_NO LIKE ? || '%' AND A.COIL_THK >= NVL(?, '000') AND A.COIL_THK <= NVL(?, '999') AND A.COIL_WTH >= NVL(?, '00000') AND A.COIL_WTH <= NVL(?, '99999') AND (A.CUR_LOAD_LOC LIKE ? || '%' OR A.CUR_LOAD_LOC IS NULL) AND A.SPEC_STL_GRD LIKE ? || '%' AND A.TRNF_SHIFT || '&' LIKE ? || '%' AND A.TRNF_GROUP || '&' LIKE ? || '%' AND A.TRNF_REG || '&' LIKE ? || '%' AND A.TRANS_CAR_NO || '&' LIKE ? || '%' AND A.ORD_NO || '&' LIKE ? || '%' AND A.ORD_SEQ || '&' LIKE ? || '%' AND A.DLIV_DIRNO || '&' LIKE ? || '%' AND D.ORDCUST_CD || '&' LIKE ? || '%' AND A.DLIV_TP || '&' LIKE ? || '%' --AND A.COIL_DEFECTIVE || '&' LIKE :19 || '%' AND A.ORD_FL || '&' LIKE ? || '%' AND A.QK_YN || '&' LIKE ? || '%' -- AND INSTR(NVL(:22, NVL(D.PRDNM_CD, 'NULL')), NVL(D.PRDNM_CD, 'NULL')) > 0 -- AND C.XSQY || '&' like ?|| '%' -- and a.SPM_YN || '&' like :24 || '%' AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.TRNF_DTIME, 1, 8)) >= NVL(?, '0')) AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8), SUBSTR(A.TRNF_DTIME, 1, 8)) <= NVL(?, '99999999')) AND (A.SHIP_PROG_CD <> '10' or A.SHIP_PROG_CD IS NULL) -- ADD BY JUNGAN 2010-12-29 不查询盘出记录 order by a.OLD_SAMPL_NO desc ]]> 0)) then t.ACTWGT / 1000 end) TGSL, sum(case when ((substr(t.ORDNO, 1, 1) = '3' or substr(t.ORDNO, 1, 2) = 'W3' or substr(t.ORDNO, 1, 3) = 'WX3' or substr(t.ORDNO, 1, 6) = 'XGEC-3') AND (d.ORDCUST_CD in ('238948') OR instr(t.ORDNO,'S')>0)) then t.ACTWGT / 1000 end) TGZL, count(case when (substr(t.ORDNO, 1, 1) = '4' or substr(t.ORDNO, 1, 2) = 'W4' or substr(t.ORDNO, 1, 6) = 'XGEC-4') then t.ACTWGT / 1000 end) SZSL, sum(case when (substr(t.ORDNO, 1, 1) = '4' or substr(t.ORDNO, 1, 2) = 'W4' or substr(t.ORDNO, 1, 6) = 'XGEC-4') then t.ACTWGT / 1000 end) SZZL, count(case when (substr(t.ORDNO, 1, 1) = '5' or substr(t.ORDNO, 1, 2) = 'W5' or substr(t.ORDNO, 1, 6) = 'XGEC-5') then t.ACTWGT / 1000 end) LTSL, sum(case when (substr(t.ORDNO, 1, 1) = '5' or substr(t.ORDNO, 1, 2) = 'W5' or substr(t.ORDNO, 1, 6) = 'XGEC-5') then t.ACTWGT / 1000 end) LTZL FROM spm_report T, TBA01_ORD_COMM D, TBA01_ORD_LINE C WHERE '1' = '1' AND T.ORDNO = C.ORD_NO(+) AND T.ORDSEQ = C.ORD_SEQ(+) AND T.ORDNO = d.ORD_NO(+) and to_char(t.rtime,'yyyymmdd') between ? and ? group by t.spmtype,to_char(t.rtime,'yyyymmdd')) --order by select * from ( select * from a union all select '合计',a.spmtype, sum(a.hzSL), sum(a.hzzL), sum(a.RZHYSL), sum(a.RZHYzL), sum(a.RZqYSL), sum(a.RZqYzL), sum(a.XCSL), sum(a.XCzL), sum(a.TGSL), sum(a.TGzL), sum(a.SZSL), sum(a.SZzL), sum(a.LTSL), sum(a.LTzL) from a group by a.spmtype ) order by ht asc ]]>