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

## 模板下载

[模板](./mes_cx_ydm_bc_inlist_csjg_hand.sql)

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

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

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

## 原始脚本内容

```sql
CREATE OR REPLACE procedure mes_cx_ydm_bc_inlist_CSJG_hand is
  a                 number(4);
  b                 varchar(200);
  cxdata            orderdetail_v@th_cx_link%rowtype;
  prod_name_cx      varchar(200);
  prod_code_cx      varchar(200);
  sg_grade_cx       varchar(200);
  sg_gradename_cx   varchar(200);
  sg_sign_cx        varchar(200);
  sg_signname_cx    varchar(200);
  wall_thickness_cx varchar(200);
  material_code_cx  varchar(200);
  material_name_cx  varchar(200);

  cursor oporder_cursor is

    select substr(t1.bal_year_month, 0, 4) || '-' ||
            substr(t1.bal_year_month, 5, 2) bal_year_month,
           --记账日期
           '天淮中间库' ckmc, t1.judge_stove_no,
           --库存地名称,
           spec_code,
           --产品规格代码,
           spec_name,
           -- 产品规格描述,
           max(std_code) std_code,
           --执行标准代码,
           max(std_name) std_name,
           --执行标准名称,
           produccode,
           --品名代码,
           producname,
           --品名描述,
           gradecode,
           --钢种代码,
           gradename,
           --钢种名称,
           steelcode,
           --钢级代码,
           steelname,
           --钢级名称,
           0 act_count,
           --期初库存支数,
           0 act_weight,
           --期初库存重量,
           sum(act_count) act_in_count,
           --本期入库支数,
           sum(act_weight) act_in_weight,
           --本期入库重量,
           0 act_out_count,
           --本期出库支数,
           0 act_out_weight,
           --本期出库重量,
           max(t1.instock_time) instock_time,
           getislljg(t1.judge_stove_no,
                      nvl(replace(pro_order_no, '/', ''),
                          replace(get_cp_order(t1.judge_stove_no), '/', ''))) is_lljg,
           --是否来料加工,
           '加工' process_name,
           --工序,
           '否' is_ckc,
           --是否出口材
           act_dimater dimater,
           --外径
           act_height height,
           --壁厚
           nvl(replace(pro_order_no, '/', ''),
                replace(get_cp_order(t1.judge_stove_no), '/', '')) orderid,
           --合同号
           model_desc,
           -- 扣型描述
           get_cp_buyer(get_cp_order(t1.judge_stove_no)) buyer,
           --客户名称
           get_cp_pass(get_cp_order(t1.judge_stove_no)) pass --孔型
      from ydm_zc_inlist t1
     where instock_type_code in ('800701', '800702', '800706')--产出盘盈改判
        and BAL_YEAR_MONTH = '202602'
       and belong_code in ('100101', '100102', '100105')
       and supply_unit_code = '120504'
       and nvl(replace(pro_order_no, '/', ''),
               replace(get_cp_order(t1.judge_stove_no), '/', '')) not like
           'ZZBY%'
       AND T1.PLINE_CODE = 'C108'
     group by bal_year_month, spec_code,
              --产品规格代码,
              produccode,
              --品名代码,
              producname, gradecode,
              --钢种代码,
              gradename, steelcode,
              --钢级代码,
              steelname, judge_stove_no, process_desc, act_dimater,
              --外径
              act_height,
              --壁厚
              model_desc, t1.pro_order_no, t1.order_no, t1.order_seq,
              spec_code, spec_name;

  curoporder oporder_cursor%rowtype;

begin

  open oporder_cursor;
  loop
    fetch oporder_cursor
      into curoporder;
    exit when oporder_cursor%notfound;

    select count(*)
      into a
      from orderdetail_v@th_cx_link t
     where t.contractno_num = curoporder.orderid
       and material_code is not null;

    if a > 0 then
      select prod_code, prod_name, sg_grade_code, sg_grade, sg_sign_code,
             sg_sign, wall_thickness, material_code, material_name
        into prod_code_cx, prod_name_cx, sg_grade_cx, sg_gradename_cx,
             sg_sign_cx, sg_signname_cx, wall_thickness_cx, material_code_cx,
             material_name_cx
        from orderdetail_v@th_cx_link t
       where t.contractno_num = curoporder.orderid
         and material_code is not null
         and rownum = 1;

    elsif substr(curoporder.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.orderid, 3)
         and material_code is not null
         and rownum = 1;
      if a > 0 then
        select prod_code, prod_name, sg_grade_code, sg_grade, sg_sign_code,
               sg_sign, wall_thickness, material_code, material_name
          into prod_code_cx, prod_name_cx, sg_grade_cx, sg_gradename_cx,
               sg_sign_cx, sg_signname_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.orderid, 3)
           and material_code is not null
           and rownum = 1;
      end if;
    end if;

    if a > 0 then
      insert into cx_ydm_bc_inlist
        (bal_year_month, ckmc, material_no, material_name, spec_code,
         spec_name, std_code, std_name, produccode, producname, gradecode,
         gradename, steelcode, steelname, act_count, act_weight,
         act_in_count, act_in_weight, act_out_count, act_out_weight, is_lljg,
         process_name, is_ckc, dimater, height, pass, model_desc, buyer,
         orderid, bc_type, judge_stove_no, instock_time,INSERT_TIME)
      values
        (curoporder.bal_year_month, curoporder.ckmc, material_code_cx,
         material_name_cx, curoporder.spec_code,
         sg_sign_cx || '*' || wall_thickness_cx, curoporder.std_code,
         curoporder.std_name, prod_code_cx, prod_name_cx, sg_grade_cx,
         sg_gradename_cx, sg_sign_cx, sg_signname_cx, curoporder.act_count,
         curoporder.act_weight, curoporder.act_in_count,
         curoporder.act_in_weight, curoporder.act_out_count,
         curoporder.act_out_weight, curoporder.is_lljg,
         curoporder.process_name, curoporder.is_ckc, curoporder.dimater,
         curoporder.height, curoporder.pass, curoporder.model_desc,
         curoporder.buyer, curoporder.orderid, '半成品入库',
         curoporder.judge_stove_no, curoporder.instock_time,sysdate);

    else
      insert into cx_ydm_bc_inlist
        (bal_year_month, ckmc, material_no, material_name, spec_code,
         spec_name, std_code, std_name, produccode, producname, gradecode,
         gradename, steelcode, steelname, act_count, act_weight,
         act_in_count, act_in_weight, act_out_count, act_out_weight, is_lljg,
         process_name, is_ckc, dimater, height, pass, model_desc, buyer,
         orderid, bc_type, judge_stove_no, instock_time)
      values
        (curoporder.bal_year_month, curoporder.ckmc, null, null,
         curoporder.spec_code, null, curoporder.std_code,
         curoporder.std_name, null, null, null, null, null, null,
         curoporder.act_count, curoporder.act_weight,
         curoporder.act_in_count, curoporder.act_in_weight,
         curoporder.act_out_count, curoporder.act_out_weight,
         curoporder.is_lljg, curoporder.process_name, curoporder.is_ckc,
         curoporder.dimater, curoporder.height, curoporder.pass,
         curoporder.model_desc, curoporder.buyer, curoporder.orderid,
         '半成品入库', curoporder.judge_stove_no, curoporder.instock_time);
    end if;

  end loop;
  close oporder_cursor;
  commit;

  /*

  exception
    when others then
      null;*/
end mes_cx_ydm_bc_inlist_CSJG_hand;
```
