MES_DOC/MES_FIN_MONTH/FinMES202602/mes_cx_rcl_zxnew_hand.sql

CREATE OR REPLACE procedure MES_CX_RCL_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_rcl IS
  
    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,
           --合同类型
           t.producname prod_variety,
           replace(t.PRO_ORDER_NO, '/', '') orderid,
           --品名、品种
           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 = '{0}'),
                   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 = '{0}'),
               0) LC_WEIGHT,
           --量差
           sum(decode(t.act_theory_weight,
                      null,
                      t.act_weight,
                      t.act_theory_weight)) mate_weight,
           --来料重量
           (case
             when sum(input_weight) <> 0 then
              round(sum(decode(t.act_weight,
                               null,
                               t.act_theory_weight,
                               t.act_weight)) / sum(input_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,
           --换成取产销提供的视图 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
     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 = 'F'
       and t.bal_year_month in ('{0}')
       AND t.PRO_ORDER_NO NOT LIKE 'ZZBY%'
     group by t.producname,
              t.steelname,
              --钢级
              t.gradename,
              --钢号
              t.spec_name,
              --规格
              t.target,
              bal_year_month,
              pro_order_no,
              judge_stove_no;

  curoporder_rcl oporder_cursor_rcl%rowtype;

begin

  open oporder_cursor_rcl;
  loop
    fetch oporder_cursor_rcl
      into curoporder_rcl;
    exit when oporder_cursor_rcl%notfound;
    select count(*)
      into a
      from orderdetail_v@th_cx_link t
     where t.contractno_num = curoporder_rcl.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_rcl.orderid
         and material_code is not null
         and rownum = 1;
    
    elsif substr(curoporder_rcl.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_rcl.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_rcl.orderid, 3)
           and material_code is not null
           and rownum = 1;
      end if;
    end if;
    if a > 0 then
      insert into hot_heat_production_mes_gx
        (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,
         GO_THERE,
         judge_stove_no,
         PRE_PROCESS,
         DELIVERY_STATUS,
         INSERT_TIME,
         LC_WEIGHT)
      values
        (seq_mes_cx.nextval,
         curoporder_rcl.contract_type,
         prod_name_cx,
         sg_sign_cx,
         sg_grade_cx,
         outer_diam_cx || '*' || wall_thickness_cx,
         curoporder_rcl.prod_type,
         curoporder_rcl.prod_weight,
         curoporder_rcl.mate_weight,
         curoporder_rcl.steel_waste,
         curoporder_rcl.process,
         curoporder_rcl.high_tech_code,
         curoporder_rcl.high_tech_name,
         curoporder_rcl.high_prod_weight,
         curoporder_rcl.high_mate_weight,
         curoporder_rcl.estimated_unit_price,
         curoporder_rcl.estimated_cost,
         curoporder_rcl.acct_period,
         material_code_cx,
         curoporder_rcl.resource_type,
         curoporder_rcl.mate_code || '1',
         
         '',
         '0',
         MATERIAL_NAME_cx,
         curoporder_rcl.orderid,
         (case
           when material_code_cx like '%B' then
            '加工'
           when material_code_cx like '%C' then
            '热处理'
           else
            ''
         end),
         curoporder_rcl.judge_stove_no,
         '热轧',
         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_rcl.Lc_Weight);
    else
      insert into hot_heat_production_mes_gx
        (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,
         GO_THERE,
         judge_stove_no,
         lc_weight)
      values
        (seq_mes_cx.nextval,
         curoporder_rcl.contract_type,
         curoporder_rcl.prod_variety,
         curoporder_rcl.steel_level,
         curoporder_rcl.steel_no,
         curoporder_rcl.spec,
         curoporder_rcl.prod_type,
         curoporder_rcl.prod_weight,
         curoporder_rcl.mate_weight,
         curoporder_rcl.steel_waste,
         curoporder_rcl.process,
         curoporder_rcl.high_tech_code,
         curoporder_rcl.high_tech_name,
         curoporder_rcl.high_prod_weight,
         curoporder_rcl.high_mate_weight,
         curoporder_rcl.estimated_unit_price,
         curoporder_rcl.estimated_cost,
         curoporder_rcl.acct_period,
         curoporder_rcl.mate_code,
         curoporder_rcl.resource_type,
         curoporder_rcl.mate_code || '1',
         
         '',
         '0',
         null,
         curoporder_rcl.orderid,
         (case when cxdata.material_code like '%B' then '加工' when
          cxdata.material_code like '%C' then '热处理' else '' end),
         curoporder_rcl.judge_stove_no,
         curoporder_rcl.lc_weight);
    end if;
  end loop;
  close oporder_cursor_rcl;
  commit;

end mes_cx_rcl_zxnew_HAND;