'3' AND B.CUR_PROG_CD <> 'PCC' and b.cur_prog_cd is not null GROUP BY SUBSTR(B.CUR_PROG_CD,1,2) union all select SUBSTR(D.CUR_PROG_CD,1,2) PROG_CD ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'A',NVL(floor(d.ACT_WGT/xx.cnt),0),0)) INS_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'B',NVL(floor(d.ACT_WGT/xx.cnt),0),0)) WRK_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'D',NVL(floor(d.ACT_WGT/xx.cnt),0),0)) EST_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'F',NVL(floor(d.ACT_WGT/xx.cnt),0),0)) END_WGT from c_tbl02_coil_comm_d c ,c_tbl02_coil_comm d ,(select count(1) cnt,L_COIL_NO from c_tbl02_coil_comm_d x group by x.ord_no,x.ord_seq,X.L_COIL_NO) xx where '1' = '1' and c.ord_no = :13 --13 and c.ord_seq = :14 --14 and c.l_coil_no = d.coil_no and c.L_COIL_NO = xx.L_COIL_NO and d.ord_fl = '1' and c.ord_fl = '1' and d.coil_stat = '2' and d.cur_prog_cd is not null GROUP BY SUBSTR(D.CUR_PROG_CD,1,2) union all select SUBSTR(H.CUR_PROG_CD,1,2) PROG_CD ,SUM(DECODE(SUBSTR(H.CUR_PROG_CD,3,1),'A',NVL(g.ord_wgt,0),0)) INS_WGT ,SUM(DECODE(SUBSTR(H.CUR_PROG_CD,3,1),'B',NVL(g.ord_wgt,0),0)) WRK_WGT ,SUM(DECODE(SUBSTR(H.CUR_PROG_CD,3,1),'D',NVL(g.ord_wgt,0),0)) EST_WGT ,SUM(DECODE(SUBSTR(H.CUR_PROG_CD,3,1),'F',NVL(H.ACT_WGT,0),0)) END_WGT from C_TBL02_COIL_COMM_D G ,C_TBL02_COIL_COMM H ,(select min(t1.CUT_SEQ) CUT_SEQ ,t2.OLD_SAMPL_NO from C_TBL02_COIL_COMM_D t1 ,c_tbl02_coil_comm t2 WHERE t1.L_COIL_NO(+) = t2.OLD_SAMPL_NO group by t2.OLD_SAMPL_NO ) x where '1' = '1' and G.ord_no = :15 --15 and G.ord_seq = :16 --16 and G.L_COIL_NO = H.COIL_NO and h.OLD_SAMPL_NO = x.OLD_SAMPL_NO and x.CUT_SEQ = g.CUT_SEQ and G.ord_fl = '1' and H.ord_fl = '1' and H.coil_stat = '3' and H.cur_prog_cd = 'DFF' GROUP BY SUBSTR(H.CUR_PROG_CD,1,2) union all select SUBSTR(D.CUR_PROG_CD,1,2) PROG_CD ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'A',decode(NVL(c.ord_wgt,0),0,NVL(d.ACT_WGT,0),NVL(c.ord_wgt,0)),0)) INS_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'B',decode(NVL(c.ord_wgt,0),0,NVL(d.ACT_WGT,0),NVL(c.ord_wgt,0)),'C',decode(NVL(c.ord_wgt,0),0,NVL(d.ACT_WGT,0),NVL(c.ord_wgt,0)),0)) WRK_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'D',decode(NVL(c.ord_wgt,0),0,NVL(d.ACT_WGT,0),NVL(c.ord_wgt,0)),0)) EST_WGT ,SUM(DECODE(SUBSTR(D.CUR_PROG_CD,3,1),'F',decode(NVL(c.ord_wgt,0),0,NVL(d.ACT_WGT,0),NVL(c.ord_wgt,0)),0)) END_WGT from c_tbl02_coil_comm_d c ,c_tbl02_coil_comm d ,L_TBF03_SPEC_MILL TT where '1' = '1' and c.ord_no = :17 --17 and c.ord_seq = :18 --18 and c.l_coil_no = d.coil_no AND D.OLD_SAMPL_NO = TT.C_COIL_NO AND D.RCAL_CNT = TT.CAL_ID and d.ord_fl = '1' and c.ord_fl = '1' and d.coil_stat = '3' and d.cur_prog_cd is not NULL AND TT.STATUS_CD < 'D' GROUP BY SUBSTR(D.CUR_PROG_CD,1,2) union all ---------------------------------------------------------------------------------------------------------- --在库连退成品卷信息 --物料编码： ---------------------------------------------------------------------------------------------------------- select SUBSTR(F.CUR_PROG_CD,1,2) PROG_CD -------酸轧到连退子卷重量生成错误，暂时修改为 大卷重量/子卷个数--------------- /*,SUM(DECODE(SUBSTR(F.CUR_PROG_CD,3,1),'A',NVL(nvl(E.ORD_WGT,f.ACT_WGT),0),0)) INS_WGT ,SUM(DECODE(SUBSTR(F.CUR_PROG_CD,3,1),'B',NVL(nvl(E.ORD_WGT,f.ACT_WGT),0),0)) WRK_WGT ,SUM(DECODE(SUBSTR(F.CUR_PROG_CD,3,1),'D',NVL(nvl(E.ORD_WGT,f.ACT_WGT),0),0)) EST_WGT ,SUM(DECODE(SUBSTR(F.CUR_PROG_CD,3,1),'F',NVL(nvl(E.ORD_WGT,f.ACT_WGT),0),0)) END_WGT*/ ,SUM(DECODE(SUBSTR(f.CUR_PROG_CD,3,1),'A',NVL(floor(f.ACT_WGT/xx.cnt),0),0)) INS_WGT ,SUM(DECODE(SUBSTR(f.CUR_PROG_CD,3,1),'B',NVL(floor(f.ACT_WGT/xx.cnt),0),0)) WRK_WGT ,SUM(DECODE(SUBSTR(f.CUR_PROG_CD,3,1),'D',NVL(floor(f.ACT_WGT/xx.cnt),0),0)) EST_WGT ,SUM(DECODE(SUBSTR(f.CUR_PROG_CD,3,1),'F',NVL(floor(f.ACT_WGT/xx.cnt),0),0)) END_WGT -------酸轧到连退子卷重量生成错误，暂时修改为 大卷重量/子卷个数--------------- from c_tbc02_coil_comm_d e ,c_tbc02_coil_comm f ,(select count(1) cnt,L_COIL_NO from c_tbc02_coil_comm_d x group by/* x.ord_no,x.ord_seq,*/X.L_COIL_NO) xx where '1' = '1' and e.ord_no = :19 --19 and e.ord_seq = :20 --20 and e.L_COIL_NO = f.OLD_SAMPL_NO and e.l_coil_no = xx.l_coil_no and e.ord_fl = '1' and f.ord_fl = '1' and f.coil_stat = '2' and f.cur_prog_cd is not null GROUP BY SUBSTR(F.CUR_PROG_CD,1,2) union all select SUBSTR(I.CUR_PROG_CD,1,2) PROG_CD ,SUM(DECODE(SUBSTR(I.CUR_PROG_CD,3,1),'A',nvl(I.ACT_WGT,0),0)) INS_WGT ,SUM(DECODE(SUBSTR(I.CUR_PROG_CD,3,1),'B',nvl(I.ACT_WGT,0),0)) WRK_WGT ,SUM(DECODE(SUBSTR(I.CUR_PROG_CD,3,1),'D',nvl(I.ACT_WGT,0),0)) EST_WGT ,SUM(DECODE(SUBSTR(I.CUR_PROG_CD,3,1),'F',nvl(I.ACT_WGT,0),0)) END_WGT from C_TBc02_COIL_COMM_D J ,C_TBc02_COIL_COMM I wHere '1' = '1' and I.ord_no = :21 --21 and I.ord_seq = :22 --22 and J.L_COIL_NO(+) = I.OLD_SAMPL_NO --and J.ord_fl = '1' and I.ord_fl = '1' and I.coil_stat = '3' and I.cur_prog_cd = 'DFF' GROUP BY SUBSTR(I.CUR_PROG_CD,1,2) ) GROUP BY PROG_CD ]]>