# MES_CX_JG_zxNEW_HAND 存储过程（什么什么加工） ## 模板下载 [模板](./mes_cx_jg_zxnew_hand.sql) 用{0:00}表达 当前月份 替换成02 用{1:00}表达 下个月份 替换成03 用{2:00}表达 下个月份 替换成01 ## 原始脚本内容 ```sql CREATE OR REPLACE procedure MES_CX_JG_zxNEW_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 CONTRACT_TYPE,--合同类型 PROD_VARIETY,--品名、品种 STEEL_LEVEL,--钢级 STEEL_NO,--钢号 SPEC,--规格 PROD_TYPE,--产量类型 sum(PROD_WEIGHT) PROD_WEIGHT,--成品重量 sum(MATE_WEIGHT) MATE_WEIGHT,--来料重量 (case when sum(MATE_WEIGHT) <>0 then round(sum(PROD_WEIGHT)/sum(MATE_WEIGHT),4)*100 else 0 end) STEEL_WASTE,--消耗占比 '加工' PROCESS,--工序 null HIGH_TECH_CODE, null HIGH_TECH_NAME, 0 HIGH_PROD_WEIGHT, 0 HIGH_MATE_WEIGHT, 0 ESTIMATED_UNIT_PRICE, 0 ESTIMATED_COST, substr(BAL_YEAR_MONTH,0,4)||'-'||substr(BAL_YEAR_MONTH,5,2) ACCT_PERIOD, MATE_CODE||'C' MATE_CODE,--物料编码 RESOURCE_TYPE RESOURCE_TYPE,--是否来料加工 MATE_CODE || xx BILLET_CODE --钢坯编码 from ( select t.*,GET_CP_CODE2(STEEL_NO, --钢种 STEEL_LEVEL, --钢级 jhzt, --交货状态 PROD_VARIETY, --品名 MODEL_DESC, --端部 jg, ACT_DIMATER, -- 外径 ACT_HEIGHT) MATE_CODE--壁厚 ,(case when (select max(judge_stove_no) from cxuser.QCM_ZG_JUGDE_APPLY t1 where T1.IS_FLAG = '0' AND T1.IS_ALL_SCRAP IN ('0', '2') AND ((T1.VALIDFLAG = '20' AND T1.TARGET <> 'A' AND T1.JUDGE_RESULT_CODE IN ('40740701', '40740705', '40740709')) OR (T1.VALIDFLAG = '20' AND T1.TARGET = 'A' AND T1.JUDGE_TOLRESULT_CODE IN ('40740701', '40740705', '40740709')) OR T1.INSTANCY_FLAG = '1') AND T1.PROCESS_CODE = 'F' --AND T.BAL_YEAR_MONTH = to_char(sysdate,'yyyymm') --AND substr(T.BAL_YEAR_MONTH,0,4) ='2024' AND T1.BAL_YEAR_MONTH = '202411' and T1.PLINE_CODE = 'C091' and T1.judge_stove_no(+) = t.judge_stove_no and ROWNUM=1) is null then 'A' ELSE 'B' END) xx, (case when t.Judge_Stove_No like 'W%' then '是' else '否' end) RESOURCE_TYPE from ( select t.pro_order_no, t.pline_code, t.pline_name, t.producname PROD_VARIETY,--品名、品种, t.steelname STEEL_LEVEL,--钢级, t.GRADENAME STEEL_NO,--钢号, t.spec_name SPEC,--规格, t.model_desc, t.std_name, T.BAL_YEAR_MONTH, t.ACT_DIMATER, t.ACT_HEIGHT, t.judge_stove_no, (case when T.TARGET ='A' THEN '产成品' else '半成品' end) PROD_TYPE,--产量类型 (SELECT DECODE(MAX(S.ORDER_TYP), '120108', '双经销', '120109', '定销订购', '120107', '来料加工', '自用') FROM cxuser.SLM_ORDER_HEAD S WHERE S.ORDER_NO = REGEXP_REPLACE(T.PRO_ORDER_NO, '/.*', '')) CONTRACT_TYPE,--合同类型 是否双经销, TO_CHAR(max(t.JUDGE_RESULT_TIME), 'YYYY-MM-DD') JUDGE_RESULT_TIME, SUM(T.ACT_COUNT) 合格支, SUM(DECODE(t.ACT_WEIGHT, NULL, t.ACT_THEORY_WEIGHT, t.ACT_WEIGHT)) PROD_WEIGHT,--成品重量--HGD SUM(T.INPUT_COUNT) 出库支, SUM(NVL(t.INPUT_WEIGHT, 0)) + SUM(NVL(T1.COUPLING_WT, 0)) + SUM(NVL(T1.COUPLING_FAIL_WT, 0)) MATE_WEIGHT, --来料重量 --出库吨 GET_DELIVERY_STATE2((select max(y.desgin_key) from cxuser.craft_ord_design_msc_pl y where y.desgin_key in (select x.desgin_key from cxuser.craft_ord_design_msc x where x.ord_ln_pk in (select b.ord_ln_pk from cxuser.slm_order_line b where b.ord_pk in (select a.ord_pk from cxuser.slm_order_head a where a.order_no = substr(t.pro_order_no,1,instr(t.pro_order_no,'/')-1)) and x.order_seq = substr(t.pro_order_no,instr(t.pro_order_no,'/')+1,3)))), (select max(y.msc_pline) from cxuser.craft_ord_design_msc_pl y where y.desgin_key in (select x.desgin_key from cxuser.craft_ord_design_msc x where x.ord_ln_pk in (select b.ord_ln_pk from cxuser.slm_order_line b where b.ord_pk in (select a.ord_pk from cxuser.slm_order_head a where a.order_no = substr(t.pro_order_no,1,instr(t.pro_order_no,'/')-1)) and x.order_seq = substr(t.pro_order_no,instr(t.pro_order_no,'/')+1,3))))) jhzt, (select max(y.value_text) from cxuser.craft_ord_design_std_gic y where y.desgin_key in (select x.desgin_key from cxuser.craft_ord_design_msc x where x.ord_ln_pk in (select b.ord_ln_pk from cxuser.slm_order_line b where b.ord_pk in (select a.ord_pk from cxuser.slm_order_head a where a.order_no = substr(t.pro_order_no,1,instr(t.pro_order_no,'/')-1)) and x.order_seq = substr(t.pro_order_no,instr(t.pro_order_no,'/')+1,3))) AND SPECL_FL = '2') jg from cxuser.QCM_ZG_JUGDE_APPLY t, (SELECT T1.HEAT_PLAN_NO, T1.JUDGE_STOVE_NO, T1.BATCH_NO, SUM(NVL(T1.COUPLING_WT, 0)) COUPLING_WT, SUM(NVL(T1.COUPLING_FAIL_WT, 0)) COUPLING_FAIL_WT FROM cxuser.MCH_TWISTINGLY_RESULT_D T1 GROUP BY T1.HEAT_PLAN_NO, T1.JUDGE_STOVE_NO, T1.BATCH_NO) T1, (SELECT A.BASENAME, B.PLINE_CODE, B.PLINE_NAME FROM cxuser.COM_BASE_INFO A, cxuser.COM_BASE_PLINE B WHERE A.BASECODE = B.FACTORY_CODE) C where T.IS_FLAG = '0' AND T.IS_ALL_SCRAP IN ('0', '2') AND ((T.VALIDFLAG = '20' AND T.TARGET <> 'A' AND T.JUDGE_RESULT_CODE IN ('40740701', '40740705', '40740709')) OR (T.VALIDFLAG = '20' AND T.TARGET = 'A' AND T.JUDGE_TOLRESULT_CODE IN ('40740701', '40740705', '40740709')) OR T.INSTANCY_FLAG = '1') AND T.PROCESS_CODE = 'G' AND T.PLINE_NAME = C.PLINE_NAME(+) AND T.HEAT_PLAN_NO = T1.HEAT_PLAN_NO(+) AND T.JUDGE_STOVE_NO = T1.JUDGE_STOVE_NO(+) AND T.BATCH_NO = T1.BATCH_NO(+) --tzh --AND T.BAL_YEAR_MONTH = to_char(sysdate,'yyyymm') -- AND substr(T.BAL_YEAR_MONTH,0,4) ='2024' AND T.BAL_YEAR_MONTH = '202411' AND t.PLINE_NAME in ('天淮加工1#线','天淮加工2#线') group by t.pro_order_no, t.pline_code, t.pline_name, t.producname, t.steelname, t.GRADENAME, t.spec_name, t.model_desc, C.BASENAME, t.std_name, t.target, T.BAL_YEAR_MONTH, t.ACT_DIMATER, t.ACT_HEIGHT, t.judge_stove_no ) t ) group by CONTRACT_TYPE,--合同类型 PROD_VARIETY,--品名、品种 STEEL_LEVEL,--钢级 STEEL_NO,--钢号 SPEC,--规格 PROD_TYPE,--产量类型 BAL_YEAR_MONTH, MATE_CODE, xx, RESOURCE_TYPE;*/ select (select decode(max(s.order_typ), '120108', '双经销', '120109', '定销订购', '120107', '来料加工', '自用') from cxuser.slm_order_head s where s.order_no = regexp_replace(t.pro_order_no, '/.*', '')) contract_type, --合同类型 replace(t.PRO_ORDER_NO, '/', '') orderid, t.producname prod_variety, --品名、品种, t.steelname steel_level, --钢级, t.gradename steel_no, --钢号, t.spec_name spec, --规格, (case when t.target = 'A' then '产成品' else '半成品' end) prod_type, --产量类型 (case when t.target = 'A' then (sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) + nvl((select SUM(nvl(T2.act_weight, 0)) from ydm_zc_stocktakinglist t2 where t2.judge_stove_no = t.judge_stove_no and t2.STOCKTAKING_TYPE_CODE = '800603' and t2.BAL_YEAR_MONTH = '202602'), 0)) else sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) end) prod_weight, --成品重量 nvl((select SUM(nvl(T2.act_weight, 0)) from ydm_zc_stocktakinglist t2 where t2.judge_stove_no = t.judge_stove_no and t2.STOCKTAKING_TYPE_CODE = '800603' and t2.BAL_YEAR_MONTH = '202602'), 0) LC_WEIGHT, sum(nvl(t.input_weight, 0)) + sum(nvl(t1.coupling_wt, 0)) + sum(nvl(t1.coupling_fail_wt, 0)) mate_weight, /*sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) mate_weight,*/ --来料重量 /*case when (sum(nvl(t.input_weight, 0)) + sum(nvl(t1.coupling_wt, 0)) + sum(nvl(t1.coupling_fail_wt, 0))) != 0 then (case when sum(nvl(t.input_weight, 0)) + sum(nvl(t1.coupling_wt, 0)) + sum(nvl(t1.coupling_fail_wt, 0)) <> 0 then round(sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) / sum(nvl(t.input_weight, 0)) + sum(nvl(t1.coupling_wt, 0)) + sum(nvl(t1.coupling_fail_wt, 0)), 4) * 100 else 0 end) else 0 end steel_waste,*/ NVL(round(sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) * 100 / NULLIF(sum(nvl(t.input_weight, 0)) + sum(nvl(t1.coupling_wt, 0)) + sum(nvl(t1.coupling_fail_wt, 0)), 0), 4), 0) as steel_waste, --消耗占比 case max(t.pline_code) when 'C108' then '车丝' else '加工' end as process, --工序 null high_tech_code, null high_tech_name, 0 high_prod_weight, 0 high_mate_weight, 0 estimated_unit_price, 0 estimated_cost, substr(bal_year_month, 0, 4) || '-' || substr(bal_year_month, 5, 2) acct_period, --GET_CP_CODE4(t.pro_order_no) MATE_CODE,--物料编码 --换成取产销提供的视图 tzh 24.12.4 get_cp_code6(t.pro_order_no) mate_code, --物料编码 getislljg(t.judge_stove_no, replace(t.PRO_ORDER_NO, '/', '')) resource_type, t.judge_stove_no, --是否来料加工 null billet_code --钢坯编码 from cxuser.qcm_zg_jugde_apply t, (select t1.heat_plan_no, t1.judge_stove_no, t1.batch_no, sum(nvl(t1.coupling_wt, 0)) coupling_wt, sum(nvl(t1.coupling_fail_wt, 0)) coupling_fail_wt from cxuser.mch_twistingly_result_d t1 group by t1.heat_plan_no, t1.judge_stove_no, t1.batch_no) t1, (select a.basename, b.pline_code, b.pline_name from cxuser.com_base_info a, cxuser.com_base_pline b where a.basecode = b.factory_code) c where t.is_flag = '0' and t.is_all_scrap in ('0', '2') and ((t.validflag = '20' and t.target <> 'A' and t.judge_result_code in ('40740701', '40740705', '40740709')) or (t.validflag = '20' and t.target = 'A' and t.judge_tolresult_code in ('40740701', '40740705', '40740709')) or t.instancy_flag = '1') and t.process_code = 'G' and t.pline_name = c.pline_name(+) and t.heat_plan_no = t1.heat_plan_no(+) and t.judge_stove_no = t1.judge_stove_no(+) and t.batch_no = t1.batch_no(+) and t.bal_year_month in ('202602') --and t.pline_name in ('天淮加工1#线', '天淮加工2#线','天淮外委加工线','天淮探伤简易线','套管线01#（635套管线）') --and t.pline_name = '天淮加工3#线' and t.PRO_ORDER_NO not like 'ZZBY%' --AND t.judge_stove_no LIKE 'F%' /*group by t.pro_order_no, t.pline_code, t.pline_name, t.producname, t.steelname, t.GRADENAME, t.spec_name, t.model_desc, C.BASENAME, t.std_name, t.target, T.BAL_YEAR_MONTH, t.ACT_DIMATER, t.ACT_HEIGHT, t.judge_stove_no*/ group by t.producname, t.steelname, --钢级 t.gradename, --钢号 t.spec_name, --规格 t.target, bal_year_month, pro_order_no, t.judge_stove_no; curoporder_jg oporder_cursor_jg%rowtype; cnt number; begin cnt := 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, LC_WEIGHT) values (seq_mes_cx.nextval, curoporder_jg.contract_type, prod_name_cx, sg_sign_cx, sg_grade_cx, outer_diam_cx || '*' || wall_thickness_cx, curoporder_jg.prod_type, curoporder_jg.prod_weight, curoporder_jg.mate_weight, curoporder_jg.steel_waste, 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 curoporder_jg.mate_code like '%B' then '热轧' when curoporder_jg.mate_code 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, curoporder_jg.LC_WEIGHT); 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, LC_WEIGHT) values (seq_mes_cx.nextval, curoporder_jg.contract_type, curoporder_jg.prod_variety, curoporder_jg.steel_level, curoporder_jg.steel_no, curoporder_jg.spec, curoporder_jg.prod_type, curoporder_jg.prod_weight, curoporder_jg.mate_weight, curoporder_jg.steel_waste, 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' when curoporder_jg.mate_code like '%H' OR curoporder_jg.mate_code like '%G' then curoporder_jg.mate_code || '3' end), '', '0', null, curoporder_jg.orderid, curoporder_jg.judge_stove_no, curoporder_jg.LC_WEIGHT); end if; end loop; close oporder_cursor_jg; commit; /*exception when others then DBMS_OUTPUT.PUT_LINE('错误: ' || cnt || '条' || SQLERRM);*/ end mes_cx_jg_zxnew_HAND; ```