MES_DOC/MES_FIN_MONTH/FinMES202602/mes_cx_rcl_zzpnew_hand.sql

CREATE OR REPLACE procedure mes_cx_rcl_zzpnew_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 *
  FROM (select t.judge_stove_no,
  (case
         when t.judge_stove_no like 'W%' then
          '是'
         else
          '否'
       end) resource_type,
               t.ZY_BATCH_ID 生产批号,
               T.STOVE_NO,
               '2026-02' acct_period,
               null high_tech_code, null high_tech_name, 0 high_prod_weight,
       0 high_mate_weight, 0 estimated_unit_price, 0 estimated_cost,
               T.PRODUCNAME,
               t.gradename steel_no,
               T.DECISION,
               T.OUTDIAMETER || '*' || T.WALLTHICK spec,
               T.ORDER_NO || T.ORDER_SEQ orderid,
               (SELECT DECODE(MAX(S.ORDER_TYP),
                              '120108',
                              '双经销',
                              '120109',
                              '定销订购',
                              '120107',
                              '来料加工',
                              '自用')
                  FROM SLM_ORDER_HEAD S
                 WHERE S.ORDER_NO = REGEXP_REPLACE(T.ORDER_NO, '/.*', '')) contract_type,
               MAX(T.STD_STYLE_DESC) 标准类别,
               MAX(T.GOWHERE_NAME) 物流去向,
               MAX(T.OUTPUT_STANDARD) 输出标准,
               MAX(T.STEELNAME) steel_level,
               MAX(T.MODEL_DESC) 扣型,
               T.PLINE_CODE,
               (SELECT C.PLINE_NAME
                  FROM COM_BASE_PLINE C
                 WHERE C.PLINE_CODE = T.PLINE_CODE) PLINE_NAME,
               sum(a.act_count) act_count,
               sum(a.act_weight) act_weight,
               null billet_code

          from PLN_ZY_RCL_M t,
               (select t.judge_stove_no,
                       T.HEAT_PLAN_NO,
                       sum(t.act_count) act_count,
                       sum(decode(T.act_weight,
                                  '',
                                  t.act_theory_weight,
                                  t.act_weight)) act_weight
                  from PORT_HTT_BATCH_SAMPLE_RESULT t
                 where (t.group_flag = 0 OR T.GROUP_FLAG IS NULL)
                      --AND T.INSTORE_FLAG = '0'
                   AND T.PROCESS_SEQ = '1'
                   AND T.MAT_STATUS IN ('00', '20')
                   and t.bal_year_month <= '202602'
                   and t.bal_year_month >= '202002'

                 group by t.judge_stove_no, T.HEAT_PLAN_NO

                UNION ALL

                select t.judge_stove_no,
                       T.HEAT_PLAN_NO,
                       -SUM(T.ACT_COUNT) act_count,
                       -sum(t.ACT_WEIGHT) act_weight
                  from QCM_ZG_JUGDE_APPLY t
                 where ((t.validflag = '20' and
                       t.JUDGE_RESULT_CODE in ('40740701', '40740709')) OR
                       T.INSTANCY_FLAG = '1')
                   and t.is_flag = 0
                   and t.PROCESS_CODE = 'F'
                   and t.bal_year_month <= '202602'
                   and t.bal_year_month >= '202002'

                 group by t.judge_stove_no, T.HEAT_PLAN_NO

                ) a
         where a.judge_stove_no = t.judge_stove_no
           AND T.HEAT_PLAN_NO = A.HEAT_PLAN_NO

         group by t.judge_stove_no,
                  t.ZY_BATCH_ID,
                  T.STOVE_NO,
                  T.PRODUCNAME,
                  T.DECISION,
                  t.gradename,
                  T.OUTDIAMETER,
                  T.WALLTHICK,
                  T.PLINE_CODE,
                  T.ORDER_NO,
                  T.ORDER_SEQ
         order by t.pline_code, t.judge_stove_no)
 WHERE act_count > 0 and orderid not like '%ZZBY%';

  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, go_there, mate_name,
         orderid,judge_stove_no,PRE_PROCESS,DELIVERY_STATUS,INSERT_TIME)
      values
        (seq_mes_cx.nextval, curoporder_rcl.contract_type, prod_name_cx,
         sg_sign_cx, sg_grade_cx,
         outer_diam_cx || '*' || wall_thickness_cx, '在产品', null,
         curoporder_rcl.act_weight, null, '热处理',
         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, material_code_cx || '1', '', '0',
         (case
            when material_code_cx like '%B' then
             '加工'
            when material_code_cx like '%C' then
             '热处理'
            else
             ''
          end), MATERIAL_NAME_cx, curoporder_rcl.orderid,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);
    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, go_there, mate_name,
         orderid,judge_stove_no)
      values
        (seq_mes_cx.nextval, curoporder_rcl.contract_type,
         null, null,
         null, null, '在产品', null,
         curoporder_rcl.act_weight, null, '热处理', 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, null,
         curoporder_rcl.resource_type, null, '', '0',
         (case when cxdata.material_code like '%B' then '加工' when
           cxdata.material_code like '%C' then '热处理' else '' end), null,
         curoporder_rcl.orderid,curoporder_rcl.judge_stove_no);
    end if;
  end loop;
  close oporder_cursor_rcl;
  commit;

end mes_cx_rcl_zzpnew_hand;