CREATE OR REPLACE procedure mes_cx_JG_zzpnew_hand is a number(4); b varchar(200); cxdata orderdetail_v@th_cx_link%rowtype; prod_name_cx varchar(200); sg_grade_cx varchar(200); sg_sign_cx varchar(200); wall_thickness_cx varchar(200); material_code_cx varchar(200); MATERIAL_NAME_cx varchar(200); outer_diam_cx varchar(200); --加工 cursor oporder_cursor_jg is select j.*,get_cp_code6(j.orderid) mate_code,case j.pline_code when 'C108' then '车丝' else '加工' end as process, (select decode(max(s.order_typ), '120108', '双经销', '120109', '定销订购', '120107', '来料加工', '自用') from cxuser.slm_order_head s where s.order_no = regexp_replace(j.orderid, '/.*', '')) contract_type, (case when j.judge_stove_no like 'W%' then '是' else '否' end) resource_type, null high_tech_code, null high_tech_name, 0 high_prod_weight, 0 high_mate_weight, 0 estimated_unit_price, 0 estimated_cost, '{3}' acct_period, get_pl_code(j.steel_no, (select xxx.dimater from cxuser.ydm_gp_outlist xxx where xxx.JUDGE_STOVE_NO = j.JUDGE_STOVE_NO and rownum = 1) --规格 ) billet_code from (SELECT JUDGE_STOVE_NO,replace(PRO_ORDER_NO, '/', '') orderid,BATCH_NO,PRODUCNAME prod_variety,STEELNAME steel_level,GRADENAME steel_no,DIMATER,HEIGHT,DIMATER || '*' || HEIGHT spec,MODEL_DESC,PLINE_CODE,PLINE_NAME,sum(END_NUM) END_NUM,sum(END_WT) mate_weight FROM (SELECT '{0}' BAL_YEAR_MONTH, PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, DIMATER, HEIGHT, PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, MAX(WATING_DECISION_MEMO) WATING_DECISION_MEMO, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME, SUM(ACT_COUNT) ACT_COUNT, SUM(ACT_WEIGHT) ACT_WEIGHT, SUM(SOURCE_NUM_TOTAL) SOURCE_NUM_TOTAL, SUM(SOURCE_WT_TOTAL) SOURCE_WT_TOTAL, SUM(SOURCE_NUM_TOTAL) SOURCE_NUM_TOTAL2, SUM(SOURCE_WT_TOTAL) SOURCE_WT_TOTAL2, SUM(NVL(SOURCE_NUM0, 0)) SOURCE_NUM0, SUM(NVL(SOURCE_WT0, 0)) SOURCE_WT0, SUM(NVL(SOURCE_NUM1, 0)) SOURCE_NUM1, SUM(NVL(SOURCE_WT1, 0)) SOURCE_WT1, SUM(NVL(SOURCE_NUM2, 0)) SOURCE_NUM2, SUM(NVL(SOURCE_WT2, 0)) SOURCE_WT2, SUM(JUDGE_NUM) JUDGE_NUM, SUM(JUDGE_WT) JUDGE_WT, SUM(FAIL_NUM) FAIL_NUM, SUM(FAIL_WT) FAIL_WT, SUM(GROUP_NUM) GROUP_NUM, SUM(GROUP_WT) GROUP_WT, SUM(END_NUM) END_NUM, SUM(END_WT) END_WT, SUM(NVL(COUPLING_NUM, 0)) COUPLING_NUM, SUM(NVL(COUPLING_WT, 0)) COUPLING_WT, SUM(NVL(XS_ACT_COUNT,0)) XS_ACT_COUNT, SUM(NVL(XS_ACT_WEIGHT,0)) XS_ACT_WEIGHT, (SUM(JUDGE_NUM) - SUM(NVL(XS_ACT_COUNT,0))) CZ_NUM, (SUM(JUDGE_WT) - SUM(NVL(XS_ACT_WEIGHT,0))) CZ_WEIGHT FROM (SELECT ---期初 PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, DIMATER, HEIGHT, PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME, ACT_COUNT, ACT_WEIGHT, '' WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM YDM_HTT_INITIAL T WHERE T.BAL_YEAR_MONTH = '{0}' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT -- 上料 PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, T.PRO_NUM SOURCE_NUM_TOTAL, T.PRO_WT SOURCE_WT_TOTAL, DECODE(MAT_SOURCE, '0', PRO_NUM, 0) SOURCE_NUM0, DECODE(MAT_SOURCE, '0', PRO_WT, 0) SOURCE_WT0, DECODE(MAT_SOURCE, '1', PRO_NUM, 0) SOURCE_NUM1, DECODE(MAT_SOURCE, '1', PRO_WT, 0) SOURCE_WT1, DECODE(MAT_SOURCE, '2', PRO_NUM, 0) SOURCE_NUM2, DECODE(MAT_SOURCE, '2', PRO_WT, 0) SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM MCH_LOADING_RESULT T, PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO AND T.BAL_YEAR_MONTH = '{0}' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT -- 成品红冲 利库短节备料 计入收料 缴库 PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, T.ACT_DIMATER DIMATER, T.ACT_HEIGHT HEIGHT, T.PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, T.ACT_COUNT SOURCE_NUM_TOTAL, T.ACT_WEIGHT SOURCE_WT_TOTAL, T.ACT_COUNT SOURCE_NUM0, T.ACT_WEIGHT SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, ACT_COUNT JUDGE_NUM, ACT_WEIGHT JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM QCM_ZG_JUGDE_APPLY T WHERE T.IS_ALL_SCRAP = '5' AND T.BAL_YEAR_MONTH = '{0}' AND T.PROCESS_CODE = 'G' AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR EXISTS (SELECT 1 FROM QCM_BC_PASS_APPLY WHERE JUGDE_APPLY_CODE = T.JUGDE_APPLY_CODE AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO AND BATCH_NO = T.BATCH_NO AND VALIDFLAG = '1' AND TYPE = '1')) AND TARGET <> 'A') OR (TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR JUDGE_TOLRESULT_CODE = '40740709'))) AND T.IS_FLAG = '0' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT ---- 当月审核的废品 和当月组炉 M.PRODUCCODE, M.PRODUCNAME, M.STEELCODE, M.STEELNAME, M.GRADECODE, M.GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, M.MODEL_CODE, M.MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, DECODE(T.MAT_STATUS, '10', 1, '40', 1, 0) FAIL_NUM, DECODE(T.MAT_STATUS, '10', T.ACT_THEORY_WEIGHT, '40', T.ACT_THEORY_WEIGHT, 0) FAIL_WT, DECODE(T.MAT_STATUS, '30', 1, 0) GROUP_NUM, DECODE(T.MAT_STATUS, '30', T.ACT_THEORY_WEIGHT, 0) GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM PORT_MCH_BATCH_SAMPLE_RESULT T, PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO AND T.VALIDFLAG = '1' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') AND IS_FAIL_CHECK = '1' AND BAL_YEAR_MONTH_FAIL IS NOT NULL AND BAL_YEAR_MONTH_FAIL = '{0}' UNION ALL SELECT --- 当月判定支 M.PRODUCCODE, M.PRODUCNAME, M.STEELCODE, M.STEELNAME, M.GRADECODE, M.GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, M.MODEL_CODE, M.MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, ACT_COUNT JUDGE_NUM, ACT_WEIGHT JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM QCM_ZG_JUGDE_APPLY T, PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO AND T.BAL_YEAR_MONTH = '{0}' AND T.PROCESS_CODE = 'G' AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR EXISTS (SELECT 1 FROM QCM_BC_PASS_APPLY WHERE JUGDE_APPLY_CODE = T.JUGDE_APPLY_CODE AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO AND BATCH_NO = T.BATCH_NO AND VALIDFLAG = '1' AND TYPE = '1')) AND TARGET <> 'A') OR (TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR JUDGE_TOLRESULT_CODE = '40740709'))) AND T.IS_FLAG = '0' AND T.IS_ALL_SCRAP IN ('0', '2', '7') and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT ---已结算的期末 PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, DIMATER, HEIGHT, PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, MEMO WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, ACT_COUNT END_NUM, ACT_WEIGHT END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM YDM_HTT_INITIAL T WHERE T.BAL_YEAR_MONTH = '{1}' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT --- 未结算的期末 M.PRODUCCODE, M.PRODUCNAME, M.STEELCODE, M.STEELNAME, M.GRADECODE, M.GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, M.MODEL_CODE, M.MODEL_DESC, M.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, M.WAITING_DECISION_MEMO WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, T.END_NUM, T.END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM (SELECT SUM(END_NUM) END_NUM, SUM(END_WT) END_WT, HEAT_PLAN_NO, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME FROM (SELECT T.HEAT_PLAN_NO, T.JUDGE_STOVE_NO, T.PHY_BATCH_NO BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, T.ACT_COUNT * T.OUTNUM_CUT END_NUM, ACT_THEORY_WEIGHT END_WT FROM PORT_MCH_BATCH_SAMPLE_RESULT T WHERE T.BAL_YEAR_MONTH < '{1}' AND T.BAL_YEAR_MONTH > '201810' AND T.VALIDFLAG = '1' AND T.MAT_STATUS = '20' UNION ALL SELECT T.HEAT_PLAN_NO, T.JUDGE_STOVE_NO, T.PHY_BATCH_NO BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, T.ACT_COUNT * T.OUTNUM_CUT END_NUM, ACT_THEORY_WEIGHT END_WT FROM PORT_MCH_BATCH_SAMPLE_RESULT T WHERE T.BAL_YEAR_MONTH < '{1}' AND T.BAL_YEAR_MONTH > '201810' AND T.BAL_YEAR_MONTH_FAIL >= '{1}' AND T.VALIDFLAG = '1' AND T.MAT_STATUS = '30' UNION ALL SELECT T.HEAT_PLAN_NO, T.JUDGE_STOVE_NO, T.PHY_BATCH_NO BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, T.ACT_COUNT * T.OUTNUM_CUT END_NUM, ACT_THEORY_WEIGHT END_WT FROM PORT_MCH_BATCH_SAMPLE_RESULT T WHERE T.BAL_YEAR_MONTH < '{1}' AND T.BAL_YEAR_MONTH > '201807' AND T.VALIDFLAG = '1' AND T.MAT_STATUS IN ('10', '40') AND (T.IS_FAIL_CHECK = '0' OR T.BAL_YEAR_MONTH_FAIL >= '{1}') UNION ALL SELECT T.HEAT_PLAN_NO, T.JUDGE_STOVE_NO, T.PHY_BATCH_NO BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, T.ACT_COUNT * T.OUTNUM_CUT END_NUM, ACT_THEORY_WEIGHT END_WT FROM PORT_MCH_BATCH_SAMPLE_RESULT T WHERE T.BAL_YEAR_MONTH < '{1}' AND T.BAL_YEAR_MONTH > '201807' AND T.VALIDFLAG = '1' AND T.MAT_STATUS = '00' AND NOT EXISTS (SELECT 1 FROM QCM_ZG_JUGDE_APPLY QCM WHERE 1 = 1 AND IS_FLAG = '0' AND IS_ALL_SCRAP IN ('0', '2', '5', '7') AND PROCESS_CODE = 'G' AND (((JUDGE_RESULT_CODE = '40740701' OR JUDGE_RESULT_CODE = '40740709' OR EXISTS (SELECT 1 FROM QCM_BC_PASS_APPLY WHERE JUGDE_APPLY_CODE = QCM.JUGDE_APPLY_CODE AND JUDGE_STOVE_NO = QCM.JUDGE_STOVE_NO AND BATCH_NO = QCM.BATCH_NO AND VALIDFLAG = '1' AND TYPE = '1')) AND TARGET <> 'A') OR (TARGET = 'A' AND (JUDGE_TOLRESULT_CODE = '40740701' OR JUDGE_TOLRESULT_CODE = '40740709'))) AND BAL_YEAR_MONTH < '{1}' AND JUGDE_APPLY_CODE = NVL(T.JUGDE_APPLY_CODE, '/') AND JUDGE_STOVE_NO = T.JUDGE_STOVE_NO AND BATCH_NO = T.PHY_BATCH_NO)) GROUP BY HEAT_PLAN_NO, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME) T, PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO AND NOT EXISTS (SELECT 1 FROM YDM_HTT_INITIAL WHERE PLINE_CODE = T.PLINE_CODE AND BAL_YEAR_MONTH = '{1}' AND ROWNUM = 1) and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT --- 当月接箍个数 M.PRODUCCODE, M.PRODUCNAME, M.STEELCODE, M.STEELNAME, M.GRADECODE, M.GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, M.MODEL_CODE, M.MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, COUPLING_NUM COUPLING_NUM, COUPLING_WT COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM MCH_TWISTINGLY_RESULT T, PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO AND T.BAL_YEAR_MONTH = '{0}' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT -- 短节精整利库 或返线 的 加上料支数 M.PRODUCCODE, M.PRODUCNAME, M.STEELCODE, M.STEELNAME, M.GRADECODE, M.GRADENAME, M.OUTDIAMETER DIMATER, M.WALLTHICK HEIGHT, M.ORDER_NO || '/' || M.ORDER_SEQ PRO_ORDER_NO, M.MODEL_CODE, M.MODEL_DESC, M.JUDGE_STOVE_NO, (SELECT MAX(batch_no) FROM pln_zy_jgx_c WHERE heat_plan_no=M.heat_plan_no ) , T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, M.PLN_INSINGLE_NUM SOURCE_NUM_TOTAL, M.PLN_INSINGLE_WT SOURCE_WT_TOTAL, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'0',M.PLN_INSINGLE_NUM,0) SOURCE_NUM0, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'0',M.PLN_INSINGLE_WT,0) SOURCE_WT0, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'1',M.PLN_INSINGLE_NUM,0) SOURCE_NUM1, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'1',M.PLN_INSINGLE_WT,0) SOURCE_WT1, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'2',M.PLN_INSINGLE_NUM,0) SOURCE_NUM2, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD),'2',M.PLN_INSINGLE_WT,0) SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM , 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM PLN_ZY_JGX_M T,PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD AND M.IF_MATCH_ODD ='6' AND M.BAL_YEAR_MONTH = '{0}' AND EXISTS (SELECT * FROM COM_BASE_PRODUCT WHERE PRODUCCODE = M.PRODUCCODE AND PRODUC_STYLE = '405004') AND EXISTS (SELECT 1 FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO AND L.BAL_YEAR_MONTH ='{0}') AND EXISTS (SELECT 1 FROM PORT_MCH_BATCH_SAMPLE_RESULT WHERE HEAT_PLAN_NO = M.HEAT_PLAN_NO) and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL -- 部分精整返线转短节 上料支数减去 SELECT T.PRODUCCODE, T.PRODUCNAME, T.STEELCODE, T.STEELNAME, T.GRADECODE, T.GRADENAME, T.OUTDIAMETER DIMATER, T.WALLTHICK HEIGHT, T.ORDER_NO || '/' || T.ORDER_SEQ PRO_ORDER_NO, T.MODEL_CODE, T.MODEL_DESC, T.JUDGE_STOVE_NO, (SELECT MAX(batch_no) FROM pln_zy_jgx_c WHERE heat_plan_no=M.heat_plan_no ) , T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, 0- M.PLN_INSINGLE_NUM SOURCE_NUM_TOTAL, 0 - M.PLN_INSINGLE_WT SOURCE_WT_TOTAL, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'0', 0- M.PLN_INSINGLE_NUM,0) SOURCE_NUM0, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'0',0- M.PLN_INSINGLE_WT,0) SOURCE_WT0, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'1',0-M.PLN_INSINGLE_NUM,0) SOURCE_NUM1, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'1',0-M.PLN_INSINGLE_WT,0) SOURCE_WT1, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'2',0-M.PLN_INSINGLE_NUM,0) SOURCE_NUM2, DECODE((SELECT MAX(MAT_SOURCE) FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO),'2',0-M.PLN_INSINGLE_WT,0) SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM , 0 COUPLING_WT, 0 XS_ACT_COUNT, 0 XS_ACT_WEIGHT FROM PLN_ZY_JGX_M T,PLN_ZY_JGX_M M WHERE T.HEAT_PLAN_NO = M.HEAT_PLAN_NO_OLD AND M.IF_MATCH_ODD ='6' AND M.BAL_YEAR_MONTH = '{0}' AND EXISTS (SELECT * FROM COM_BASE_PRODUCT WHERE PRODUCCODE = M.PRODUCCODE AND PRODUC_STYLE = '405004') AND EXISTS (SELECT 1 FROM MCH_LOADING_RESULT L WHERE L.HEAT_PLAN_NO = T.HEAT_PLAN_NO AND L.BAL_YEAR_MONTH ='{0}') AND EXISTS (SELECT 1 FROM PORT_MCH_BATCH_SAMPLE_RESULT WHERE HEAT_PLAN_NO = M.HEAT_PLAN_NO) and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') UNION ALL SELECT T.PRODUCCODE, T.PRODUCNAME, T.STEELCODE, T.STEELNAME, T.GRADECODE, T.GRADENAME, T.ACT_DIMATER DIMATER, T.ACT_HEIGHT HEIGHT, T.PRO_ORDER_NO, T.MODEL_CODE, T.MODEL_DESC, T.JUDGE_STOVE_NO, T.BATCH_NO, T.PLINE_CODE, T.PLINE_NAME, 0 ACT_COUNT, 0 ACT_WEIGHT, '' WATING_DECISION_MEMO, 0 SOURCE_NUM_TOTAL, 0 SOURCE_WT_TOTAL, 0 SOURCE_NUM0, 0 SOURCE_WT0, 0 SOURCE_NUM1, 0 SOURCE_WT1, 0 SOURCE_NUM2, 0 SOURCE_WT2, 0 JUDGE_NUM, 0 JUDGE_WT, 0 FAIL_NUM, 0 FAIL_WT, 0 GROUP_NUM, 0 GROUP_WT, 0 END_NUM, 0 END_WT, 0 COUPLING_NUM, 0 COUPLING_WT, T.ACT_COUNT XS_ACT_COUNT, T.ACT_WEIGHT XS_ACT_WEIGHT FROM YDM_ZC_INLIST T WHERE T.BAL_YEAR_MONTH = '{0}' and t.PLINE_CODE in ('C109', 'C108', 'C092','C102','C107','C104','C100') AND INSTOCK_TYPE_CODE != '800713' ) WHERE 1 = 1 GROUP BY PRODUCCODE, PRODUCNAME, STEELCODE, STEELNAME, GRADECODE, GRADENAME, DIMATER, HEIGHT, PRO_ORDER_NO, MODEL_CODE, MODEL_DESC, JUDGE_STOVE_NO, BATCH_NO, PLINE_CODE, PLINE_NAME) ss WHERE END_NUM > 0 and PRO_ORDER_NO not like '%ZZBY%' group by JUDGE_STOVE_NO,PRO_ORDER_NO,BATCH_NO,PRODUCNAME,STEELNAME,GRADENAME,DIMATER,HEIGHT,MODEL_DESC,PLINE_CODE,PLINE_NAME ORDER BY PLINE_CODE, JUDGE_STOVE_NO, BATCH_NO) j ; curoporder_jg oporder_cursor_jg%rowtype; begin --先删除 /* delete from hot_production_mes_jg where acct_period in ('2025-01', '2025-01') and prod_type = '在产品' and FLAG = '0';*/ open oporder_cursor_jg; loop fetch oporder_cursor_jg into curoporder_jg; exit when oporder_cursor_jg%notfound; select count(*) into a from orderdetail_v@th_cx_link t where t.contractno_num = curoporder_jg.orderid and material_code is not null; if a > 0 then select prod_name,sg_grade,sg_sign,outer_diam,wall_thickness,material_code,MATERIAL_NAME into prod_name_cx,sg_grade_cx ,sg_sign_cx,outer_diam_cx ,wall_thickness_cx ,material_code_cx , MATERIAL_NAME_cx from orderdetail_v@th_cx_link t where t.contractno_num = curoporder_jg.orderid and material_code is not null and rownum = 1; elsif substr(curoporder_jg.orderid, 1, 2) in ('TH', 'XS') then select count(*) into a from orderdetail_v@th_cx_link t where substr(t.contractno_num, 3) = substr(curoporder_jg.orderid, 3) and material_code is not null and rownum = 1; if a > 0 then select prod_name,sg_grade,sg_sign,outer_diam,wall_thickness,material_code,MATERIAL_NAME into prod_name_cx,sg_grade_cx ,sg_sign_cx ,outer_diam_cx,wall_thickness_cx ,material_code_cx , MATERIAL_NAME_cx from orderdetail_v@th_cx_link t where substr(t.contractno_num, 3) = substr(curoporder_jg.orderid, 3) and material_code is not null and rownum = 1; end if; end if; if a > 0 then insert into hot_production_mes_jg (cx_no, contract_type, prod_variety, steel_level, steel_no, spec, prod_type, prod_weight, mate_weight, steel_waste, process, high_tech_code, high_tech_name, high_prod_weight, high_mate_weight, estimated_unit_price, estimated_cost, acct_period, mate_code, resource_type, billet_code, memo, flag,mate_name,orderid,judge_stove_no,PRE_PROCESS,DELIVERY_STATUS,INSERT_TIME) values (seq_mes_cx.nextval, curoporder_jg.contract_type, prod_name_cx, sg_sign_cx, sg_grade_cx, outer_diam_cx || '*' || wall_thickness_cx, '在产品', 0, curoporder_jg.mate_weight, 0, curoporder_jg.process, curoporder_jg.high_tech_code, curoporder_jg.high_tech_name, curoporder_jg.high_prod_weight, curoporder_jg.high_mate_weight, curoporder_jg.estimated_unit_price, curoporder_jg.estimated_cost, curoporder_jg.acct_period, material_code_cx, curoporder_jg.resource_type, --(case when curOPOrder_jg.BILLET_CODE like ), (case when curoporder_jg.mate_code like '%B' then curoporder_jg.mate_code || '1' when curoporder_jg.mate_code like '%C' then curoporder_jg.mate_code || '2' when curoporder_jg.mate_code like '%H' or curoporder_jg.mate_code like '%G' then curoporder_jg.mate_code || '3' end ), '', '0',MATERIAL_NAME_cx,curoporder_jg.orderid,curoporder_jg.judge_stove_no,(case when material_code_cx like '%B' then '热轧' when material_code_cx like '%C' then '热处理' when curoporder_jg.mate_code like '%H' or curoporder_jg.mate_code like '%G' then '加工' end),SUBSTR(MATERIAL_NAME_cx,INSTR(MATERIAL_NAME_cx, ',', 1, 1) + 1, INSTR(MATERIAL_NAME_cx, ',', 1,2) - INSTR(MATERIAL_NAME_cx, ',', 1, 1) - 1),sysdate); else insert into hot_production_mes_jg (cx_no, contract_type, prod_variety, steel_level, steel_no, spec, prod_type, prod_weight, mate_weight, steel_waste, process, high_tech_code, high_tech_name, high_prod_weight, high_mate_weight, estimated_unit_price, estimated_cost, acct_period, mate_code, resource_type, billet_code, memo, flag,mate_name,orderid,judge_stove_no) values (seq_mes_cx.nextval, curoporder_jg.contract_type, curoporder_jg.prod_variety, curoporder_jg.steel_level, curoporder_jg.steel_no, curoporder_jg.spec, '在产品', 0, curoporder_jg.mate_weight, 0, curoporder_jg.process, curoporder_jg.high_tech_code, curoporder_jg.high_tech_name, curoporder_jg.high_prod_weight, curoporder_jg.high_mate_weight, curoporder_jg.estimated_unit_price, curoporder_jg.estimated_cost, curoporder_jg.acct_period, curoporder_jg.mate_code, curoporder_jg.resource_type, --(case when curOPOrder_jg.BILLET_CODE like ), (case when curoporder_jg.mate_code like '%B' then curoporder_jg.mate_code || '1' when curoporder_jg.mate_code like '%C' then curoporder_jg.mate_code || '2' end), '', '0',null,curoporder_jg.orderid,curoporder_jg.judge_stove_no); end if; end loop; close oporder_cursor_jg; commit; /* exception when others then null;*/ end mes_cx_JG_zzpnew_hand;