# mes_cx_jg_zxnew_hand 存储过程（什么什么加工）

## 模板下载

[模板](./mes_cx_jg_zxnew_hand.sql)

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

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

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

## 原始脚本内容

```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;
```
