MES_DOC/MES_FIN_MONTH/FinMES202602/mes_cx_rz_zzpnew_hand.sql

CREATE OR REPLACE procedure mes_cx_rz_zzpnew_hand is
  a      number(4);
  b      varchar(4000);
  -- 为cxdata定义显式变量而不是使用%rowtype
  cxdata_contractno_num    varchar2(100);
  cxdata_prod_name         varchar2(200);
  cxdata_sg_sign           varchar2(100);
  cxdata_sg_grade          varchar2(100);
  cxdata_outer_diam        varchar2(50);
  cxdata_wall_thickness    varchar2(50);
  cxdata_material_code     varchar2(100);
  cxdata_material_name     varchar2(500); -- 特别关注这个字段
  cxdata orderdetail_v@th_cx_link%rowtype;

  --加工
  cursor oporder_cursor_jg is

 SELECT J.*,REPLACE(GET_CP_ORDER(J.Judge_Stove_No), '/', '') ORDERID,(select decode(max(s.order_typ),
                            '120108',
                            '双经销',
                            '120109',
                            '定销订购',
                            '120107',
                            '来料加工',
                            '自用')
               from cxuser.slm_order_head s
              where s.order_no = regexp_replace(REPLACE(GET_CP_ORDER(J.Judge_Stove_No), '/', ''), '/.*', '')) contract_type,(case
             when j.judge_stove_no like 'W%' then
              '是'
             else
              '否'
           end) resource_type,null high_tech_code, null high_tech_name,
           0 high_prod_weight, 0 high_mate_weight, 0 estimated_unit_price,
           0 estimated_cost, '{3}' acct_period,
           get_pl_code((select max(GRADENAME) from PLN_ZY_ZG_m p where p.FEED_HEAT_NO = j.judge_stove_no),
                    (select max(DIAMETER) from PLN_ZY_ZG_m p where p.FEED_HEAT_NO = j.judge_stove_no) --规格
                    ) billet_code
  FROM (select aa.JUDGE_STOVE_NO,
               aa.PLINE_NAME,
               sum(aa.act_COUNT) act_COUNT,
               sum(aa.WEIGHT) WEIGHT
          from ( --期初
                SELECT T.JUDGE_STOVE_NO,
                        T.PLINE_NAME,
                        SUM(NVL(T.ACT_COUNT, 0)) act_COUNT,
                        SUM(NVL(T.ACT_WEIGHT, 0)) WEIGHT
                  FROM YDM_ZC_INITIAL_ZG T
                 WHERE 1 = 1
                   AND t.bal_year_month = '{0}'
                 group by t.JUDGE_STOVE_NO, t.PLINE_NAME
                UNION ALL
                --入库
                select t.JUDGE_STOVE_NO,
                        t.PLINE_NAME,
                        nvl((case
                              when t.BAL_YEAR_MONTH = '{0}' then
                               nvl(t.count, 0)
                            end),
                            0) act_COUNT,
                        nvl((case
                              when t.BAL_YEAR_MONTH = '{0}' then
                               nvl(t.weight, 0)
                            end),
                            0) WEIGHT
                  from (SELECT REGEXP_REPLACE(T.JUDGE_STOVE_NO, '-.*', '') JUDGE_STOVE_NO,
                                (CEIL(T.ACT_COUNT / S.SAW_NUM)) AS count,
                                t.weight,
                                t.BAL_YEAR_MONTH,
                                t.PLINE_NAME
                           FROM (SELECT T1.JUDGE_STOVE_NO,
                                        T1.BAL_YEAR_MONTH,
                                        (SELECT C.PLINE_NAME
                                           FROM COM_BASE_PLINE C
                                          WHERE C.PLINE_CODE = T1.PLINE_CODE) PLINE_NAME,
                                        SUM(T1.ACT_COUNT) ACT_COUNT,
                                        SUM(T1.INPUT_WEIGHT) weight
                                   FROM PORT_MIL_BATCH_SAMPLE_RESULT T1
                                  WHERE T1.JUDGE_STOVE_NO =
                                        REGEXP_REPLACE(T1.JUDGE_STOVE_NO,
                                                       '-.*',
                                                       '') || '-01'
                                    AND T1.BAL_YEAR_MONTH = '{0}'

                                  GROUP BY T1.JUDGE_STOVE_NO,
                                           T1.BAL_YEAR_MONTH,
                                           T1.PLINE_CODE) T,
                                MIL_PLAN S
                          WHERE T.JUDGE_STOVE_NO = S.JUDGE_STOVE_NO
                            AND S.BACK_PLAN_FLAG = '0'
                            AND S.BACK_SLAB_FLAG = '0') t

                union all

                --出库
                select t.JUDGE_STOVE_NO,
                        t.PLINE_NAME,
                        -nvl((case
                               when t.BAL_YEAR_MONTH = '{0}' then
                                nvl(t.count, 0)
                             end),
                             0) act_COUNT,
                        -nvl((case
                               when t.BAL_YEAR_MONTH = '{0}' then
                                nvl(t.weight, 0)
                             end),
                             0) WEIGHT

                  from (SELECT JUDGE_STOVE_NO,
                                BAL_YEAR_MONTH,
                                PLINE_NAME,
                                SUM(COUNT) COUNT,
                                SUM(WEIGHT) WEIGHT
                           FROM (SELECT T1.JUDGE_STOVE_NO,
                                        T1.BAL_YEAR_MONTH,
                                        (SELECT C.PLINE_NAME
                                           FROM COM_BASE_PLINE C
                                          WHERE C.PLINE_CODE = T1.PLINE_CODE) PLINE_NAME,
                                        SUM(T1.OUTPUT_COUNT) AS COUNT,
                                        SUM(T1.OUTPUT_WEIGHT) AS WEIGHT
                                   FROM QCM_ZG_JUGDE_APPLY T1, MIL_PLAN S5
                                  WHERE T1.JUDGE_STOVE_NO || '-01' =
                                        S5.JUDGE_STOVE_NO
                                    AND S5.BACK_PLAN_FLAG = '0'
                                    AND S5.BACK_SLAB_FLAG = '0'
                                    AND T1.PROCESS_CODE = 'D'
                                    AND T1.BAL_YEAR_MONTH = '{0}'
                                    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.VALIDFLAG = '20' AND T1.TARGET = 'A' AND
                                        T1.IS_ALL_SCRAP = '1') OR
                                        T1.INSTANCY_FLAG = '1')
                                    AND T1.PLINE_CODE NOT IN ('C065', 'C066')

                                  GROUP BY T1.JUDGE_STOVE_NO,
                                           T1.BAL_YEAR_MONTH,
                                           T1.PLINE_CODE
                                 UNION ALL
                                 SELECT REGEXP_REPLACE(F.JUDGE_STOVE_NO,
                                                       '-.*',
                                                       '') JUDGE_STOVE_NO,
                                        F.BAL_YEAR_MONTH,
                                        S1.PLINE_NAME,
                                        COUNT(1) COUNT,
                                        SUM(S1.INPUT_WEIGHT) WEIGHT
                                   FROM (SELECT COUNT(1) ACOUNT,
                                                T3.JUDGE_STOVE_NO,
                                                T3.NEW_HEAT_NO,
                                                T3.BAL_YEAR_MONTH
                                           FROM (SELECT G2.*, T1.BAL_YEAR_MONTH
                                                   FROM MIL_SLAB_SCRAP     G2,
                                                        QCM_ZG_JUGDE_APPLY T1
                                                  WHERE REGEXP_REPLACE(G2.NEW_HEAT_NO,
                                                                       '-.*',
                                                                       '') =
                                                        T1.JUDGE_STOVE_NO
                                                    AND (G2.REMARK <> '无' OR
                                                         G2.REMARK IS NULL)
                                                    AND ((t1.validflag = '20' and
                                                         t1.JUDGE_RESULT_CODE in
                                                         ('40740701',
                                                           '40740705',
                                                           '40740709')) OR
                                                         T1.INSTANCY_FLAG = '1')
                                                    AND T1.IS_FLAG = '0'
                                                    AND T1.PROCESS_CODE = 'D'
                                                    AND T1.BAL_YEAR_MONTH =
                                                        '{0}'
                                                ) T3
                                          WHERE T3.PROCESS_NO <= 6
                                            AND T3.NEW_HEAT_NO IS NOT NULL
                                          GROUP BY T3.JUDGE_STOVE_NO,
                                                   T3.NEW_HEAT_NO,
                                                   T3.BAL_YEAR_MONTH) F,
                                        (SELECT SUM(S.INPUT_WEIGHT) INPUT_WEIGHT,
                                                S.JUDGE_STOVE_NO,
                                                S.M_MAT_NO,
                                                (SELECT C.PLINE_NAME
                                                   FROM COM_BASE_PLINE C
                                                  WHERE C.PLINE_CODE = S.PLINE_CODE) PLINE_NAME,
                                                ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
                                           FROM PORT_MIL_BATCH_SAMPLE_RESULT S
                                          WHERE

                                          S.TR_FLAG = '0'
                                          GROUP BY S.M_MAT_NO,
                                                   S.JUDGE_STOVE_NO,
                                                   S.PLINE_CODE) S1
                                  WHERE F.JUDGE_STOVE_NO = S1.JUDGE_STOVE_NO
                                    AND RNUM <= F.ACOUNT
                                  GROUP BY F.JUDGE_STOVE_NO,
                                           F.BAL_YEAR_MONTH,
                                           S1.PLINE_NAME
                                 UNION ALL
                                 SELECT REGEXP_REPLACE(S.JUDGE_STOVE_NO,
                                                       '-.*',
                                                       '') JUDGE_STOVE_NO,
                                        T.BAL_YEAR_MONTH,
                                        S.PLINE_NAME,
                                        COUNT(1) COUNT,
                                        SUM(S.INPUT_WEIGHT) WEIGHT
                                   FROM (SELECT G1.*, T1.BAL_YEAR_MONTH
                                           FROM MIL_FURNACES_RESLUT_DETIA G1,
                                                QCM_ZG_JUGDE_APPLY        T1
                                          WHERE REGEXP_REPLACE(G1.ZP_JUDGE_STOVE_NO,
                                                               '-.*',
                                                               '') =
                                                T1.JUDGE_STOVE_NO
                                            AND (G1.REMARK <> '无' OR
                                                 G1.REMARK IS NULL)
                                            AND ((t1.validflag = '20' and
                                                 t1.JUDGE_RESULT_CODE in
                                                 ('40740701',
                                                   '40740705',
                                                   '40740709')) OR
                                                 T1.INSTANCY_FLAG = '1')
                                            AND T1.IS_FLAG = '0'
                                            AND T1.PROCESS_CODE = 'D'
                                            AND T1.BAL_YEAR_MONTH = '{0}'
                                         ) T,
                                        (SELECT SUM(S1.INPUT_WEIGHT) INPUT_WEIGHT,
                                                S1.M_MAT_NO,
                                                S1.JUDGE_STOVE_NO,
                                                (SELECT C.PLINE_NAME
                                                   FROM COM_BASE_PLINE C
                                                  WHERE C.PLINE_CODE =
                                                        S1.PLINE_CODE) PLINE_NAME,
                                                ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
                                           FROM PORT_MIL_BATCH_SAMPLE_RESULT S1
                                          WHERE
                                          S1.TR_FLAG = '0'
                                          GROUP BY S1.JUDGE_STOVE_NO,
                                                   S1.M_MAT_NO,
                                                   S1.PLINE_CODE) S
                                  WHERE T.JUDGE_STOVE_NO = S.JUDGE_STOVE_NO
                                    AND RNUM <= T.GROUP_NUM
                                  GROUP BY S.JUDGE_STOVE_NO,
                                           S.PLINE_NAME,
                                           T.BAL_YEAR_MONTH
                                 UNION ALL
                                 SELECT REGEXP_REPLACE(T8.JUDGE_STOVE_NO,
                                                       '-.*',
                                                       '') JUDGE_STOVE_NO,
                                        T7.BAL_YEAR_MONTH,
                                        T8.PLINE_NAME,
                                        COUNT(1) COUNT,
                                        SUM(INPUT_WEIGHT) WEIGHT
                                   FROM (SELECT T.OLD_ZP_JUDGE_STOVE_NO,
                                                CEIL(SUM(T.ZP_COUNT) / S.SAW_NUM) AS RN,
                                                T1.BAL_YEAR_MONTH
                                           FROM MIL_OFFLINE_GROUP  T,
                                                MIL_PLAN           S,
                                                QCM_ZG_JUGDE_APPLY T1
                                          WHERE T.Old_Zp_Judge_Stove_No =
                                                S.JUDGE_STOVE_NO
                                            AND (T.REMARK <> '无' OR
                                                T.REMARK IS NULL)
                                            AND S.BACK_SLAB_FLAG <> '2'
                                            AND REGEXP_REPLACE(T.ZP_JUDGE_STOVE_NO,
                                                               '-.*',
                                                               '') =
                                                T1.Judge_Stove_No
                                            AND ((t1.validflag = '20' and
                                                t1.JUDGE_RESULT_CODE in
                                                ('40740701',
                                                   '40740705',
                                                   '40740709')) OR
                                                T1.INSTANCY_FLAG = '1')
                                            AND T1.IS_FLAG = '0'
                                            AND T1.PROCESS_CODE = 'D'
                                            AND T1.BAL_YEAR_MONTH = '{0}'

                                          GROUP BY T.OLD_ZP_JUDGE_STOVE_NO,
                                                   S.SAW_NUM,
                                                   T1.BAL_YEAR_MONTH) T7,
                                        (SELECT SUM(S2.INPUT_WEIGHT) INPUT_WEIGHT,
                                                S2.JUDGE_STOVE_NO,
                                                S2.M_MAT_NO,
                                                (SELECT C.PLINE_NAME
                                                   FROM COM_BASE_PLINE C
                                                  WHERE C.PLINE_CODE =
                                                        S2.PLINE_CODE) PLINE_NAME,
                                                ROW_NUMBER() OVER(PARTITION BY JUDGE_STOVE_NO ORDER BY JUDGE_STOVE_NO) RNUM
                                           FROM PORT_MIL_BATCH_SAMPLE_RESULT S2
                                          WHERE
                                          S2.TR_FLAG = '0'
                                          GROUP BY S2.M_MAT_NO,
                                                   S2.JUDGE_STOVE_NO,
                                                   S2.PLINE_CODE) T8
                                  WHERE REGEXP_REPLACE(T7.OLD_ZP_JUDGE_STOVE_NO,
                                                       '-.*',
                                                       '') || '-01' =
                                        T8.JUDGE_STOVE_NO
                                    AND RNUM <= RN
                                  GROUP BY T8.JUDGE_STOVE_NO,
                                           T7.BAL_YEAR_MONTH,
                                           T8.PLINE_NAME)
                          GROUP BY JUDGE_STOVE_NO, BAL_YEAR_MONTH, PLINE_NAME) t) aa
         group by aa.JUDGE_STOVE_NO, aa.PLINE_NAME) J
 WHERE act_COUNT > 0;

  curoporder_jg oporder_cursor_jg%rowtype;

begin

  open oporder_cursor_jg;
  loop
    fetch oporder_cursor_jg into curoporder_jg;
    exit when oporder_cursor_jg%notfound;

    begin  -- 添加内部begin块用于异常处理
      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 *
        select contractno_num, prod_name, sg_sign, sg_grade, 
               outer_diam, wall_thickness, material_code, material_name
          into cxdata_contractno_num, cxdata_prod_name, cxdata_sg_sign, 
               cxdata_sg_grade, cxdata_outer_diam, cxdata_wall_thickness,
               cxdata_material_code, cxdata_material_name
          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 contractno_num, prod_name, sg_sign, sg_grade, 
                 outer_diam, wall_thickness, material_code, material_name
            into cxdata_contractno_num, cxdata_prod_name, cxdata_sg_sign, 
                 cxdata_sg_grade, cxdata_outer_diam, cxdata_wall_thickness,
                 cxdata_material_code, cxdata_material_name
            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 INSTR(curoporder_jg.orderid, 'ZZBY') <= 0 then
        if a > 0 then
          insert into hot_production_mes_rz
            (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_jg.contract_type, cxdata_prod_name,
             cxdata_sg_sign, cxdata_sg_grade, 
             cxdata_outer_diam || '*' || cxdata_wall_thickness, '在产品', null,
             curoporder_jg.WEIGHT, null, '热轧',
             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, cxdata_material_code,
             curoporder_jg.resource_type, curoporder_jg.billet_code, '', '0',
             (case
                when cxdata_material_code like '%B' then '加工'
                when cxdata_material_code like '%C' then '热处理'
                else ''
              end), cxdata_material_name, curoporder_jg.orderid, 
             curoporder_jg.judge_stove_no, null,
             SUBSTR(cxdata_material_name, 
                    INSTR(cxdata_material_name, ',', 1, 1) + 1, 
                    INSTR(cxdata_material_name, ',', 1, 2) - INSTR(cxdata_material_name, ',', 1, 1) - 1),
             sysdate);
        else
          -- 处理没有找到数据的情况
          insert into HOT_PRODUCTION_MES_RZ
            (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_jg.contract_type,
             null, null, null, null, '在产品', null,
             curoporder_jg.WEIGHT, null, '热轧', 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, null,
             curoporder_jg.resource_type, curoporder_jg.billet_code, '', '0',
             null, null, curoporder_jg.orderid, curoporder_jg.judge_stove_no);
        end if;
      end if;
      
    exception
      when value_error then  -- 处理值错误
        -- 记录错误信息，但继续处理下一条记录
        dbms_output.put_line('数据截断错误 for orderid: ' || curoporder_jg.orderid);
        continue;
      when others then
        -- 处理其他异常
        dbms_output.put_line('其他错误 for orderid: ' || curoporder_jg.orderid || ' - ' || sqlerrm);
        continue;
    end;
    
  end loop;
  close oporder_cursor_jg;
  commit;

end mes_cx_rz_zzpnew_hand;