# mes_cx_jg_CSJG_HAND 存储过程（什么什么热轧）

## 模板下载

[模板](./mes_cx_jg_CSJG_HAND.sql)

用{0}表达 当前月份 替换成202602

用{1}表达 下个月份 替换成202603

用{2}表达 下个月份 替换成202601

用{3}表达 当前月份 替换成2026-02

## 原始脚本内容

```sql
CREATE OR REPLACE procedure mes_cx_jg_CSJG_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,
        --规格,
       '半成品' prod_type,
        --产量类型
        sum(decode(t.act_weight, null, t.act_theory_weight, t.act_weight)) prod_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,
        --消耗占比
       '加工' 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.PRO_ORDER_NO not like 'ZZBY%'
   AND t.judge_stove_no NOT LIKE 'F%'
   and t.pline_code = 'C108'
   and t.target = 'A'
/*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)
      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 '%G' OR curoporder_jg.mate_code like '%H' 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);
    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, 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' end), '', '0',null,curoporder_jg.orderid,curoporder_jg.judge_stove_no);
    end if;

  end loop;
  close oporder_cursor_jg;
  commit;



  /*exception
    when others then
      DBMS_OUTPUT.PUT_LINE('错误:  ' || cnt || '条' || SQLERRM);*/
end mes_cx_jg_CSJG_HAND;
```
