MES_DOC/MES_FIN_MONTH/FinMES202602/mes_cx_rz_zzpnew_hand.sql

CREATE OR REPLACE procedure mes_cx_ydm_zc_outlist_new_hand is
  a      number(4);
  b      varchar(200);
  gxstr  varchar(200);
    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);
      outer_diam_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,
           --期初库存重量,
           0 act_in_count,
           --本期入库支数,
           0 act_in_weight,
           --本期入库重量,
           sum(act_count) act_out_count,
           --本期出库支数,
           sum(nvl(PONDER_GROSS_WT,act_weight)) act_out_weight,
           (select nvl(sum(act_weight), 0)
                from ydm_zc_stocktakinglist t
               where t.judge_stove_no = t1.judge_stove_no
                 and t.stocktaking_type_code = '800603'
                 and t.bal_year_month = '{0}') lc,
           --本期出库重量,
           getislljg(t1.judge_stove_no,
                      nvl(replace(pro_order_no, '/', ''),
                          replace(get_cp_order(t1.judge_stove_no), '/', ''))) is_lljg,
           --是否来料加工,
           case max(PLINE_CODE) when 'C108' then '车丝' else
           (case process_desc
              when '轧制' then
               '热轧'
              when '加工线' then
               '加工'
              else
               process_desc
            end) end as process_name,
           --工序,
           
          -- '否' is_ckc,
           --是否出口材
          (case
           when nvl(mvm.if_export,'内贸') ='内贸' then
            '否'
           when nvl(mvm.if_export,'内贸') ='外贸' then
            '是'
           else
            '否'
           end) is_ckc,
           
           act_dimater dimater,
           --外径
           act_height height,
           --壁厚
           nvl(replace(pro_order_no, '/', ''),
                replace(get_cp_order(t1.judge_stove_no), '/', '')) orderid,
           --合同号
           model_desc,
           -- 扣型描述
           outstock_type_name,
           --入库类型名称
           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_outlist t1,mvm_order_matno_info mvm--增加MVN
     where nvl(replace(pro_order_no, '/', ''),
                replace(get_cp_order(t1.judge_stove_no), '/', '')) =
           mvm.CONTRACTNO_NUM
           
       and outstock_type_code in
          -- ('800805', '800804', '800802', '800808', '800806','800812')
           ('800805', '800804', '800802', '800808', '800806')
       and belong_code in ('100101', '100102', '100105')
       and supply_unit_code = '120504'
       and t1.bal_year_month in ('{0}')
       and t1.order_no || '/' || t1.order_seq not in
           ('XSXGQ-25A0432/001',
            'XSXGQ-25A0975/001',
            'XSXGQ-25A0975/002',
            'XSXKQ-25A0174/007',
            'XSXKQ-25A0174/008',
            'XSXKQ-25A0795/001')
       and nvl(replace(pro_order_no, '/', ''),
               replace(get_cp_order(t1.judge_stove_no), '/', '')) not like
           'ZZBY%'
       --and t1.judge_stove_no  like 'F%'
     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, batch_no,mvm.if_export,outstock_type_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 cxuser.ydm_zc_initial t
     where t.judge_stove_no = curoporder.judge_stove_no
       and bal_year_month = replace(curoporder.bal_year_month, '-', '');
    if a > 0 then
      select (case PLINE_CODE when 'C108' then '车丝' else
           (case process_desc
              when '轧制' then
               '热轧'
              when '加工线' then
               '加工'
              else
               process_desc
            end) end)
        into gxstr
        from cxuser.ydm_zc_initial t
       where t.judge_stove_no = curoporder.judge_stove_no
         and bal_year_month = replace(curoporder.bal_year_month, '-', '')
         and rownum = 1;
    else
      select count(*)
        into a
        from cxuser.ydm_zc_inlist t
       where t.judge_stove_no = curoporder.judge_stove_no
         and bal_year_month = replace(curoporder.bal_year_month, '-', '');
      if a > 0 then
        select (case PLINE_CODE when 'C108' then '车丝' else
           (case process_desc
              when '轧制' then
               '热轧'
              when '加工线' then
               '加工'
              else
               process_desc
            end) end)
          into gxstr
          from cxuser.ydm_zc_inlist t
         where t.judge_stove_no = curoporder.judge_stove_no
           and bal_year_month = replace(curoporder.bal_year_month, '-', '')
           and rownum = 1;
      else
        gxstr := curoporder.process_name;
      end if;
    end if;
  
    select count(*)
      into a
      from mvm_order_matno_info t
     where t.contractno_num = curoporder.orderid
       and material_code is not null;
  
    if a > 0 then
      select outer_diam,prod_code,prod_name,SG_GRADE_CODE,sg_grade,SG_SIGN_CODE,sg_sign,wall_thickness,material_code,MATERIAL_NAME
        into outer_diam_cx,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 mvm_order_matno_info 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','EX') then
      select count(*)
        into a
        from mvm_order_matno_info 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 outer_diam,prod_code,prod_name,SG_GRADE_CODE,sg_grade,SG_SIGN_CODE,sg_sign,wall_thickness,material_code,MATERIAL_NAME
        into outer_diam_cx,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 mvm_order_matno_info 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
      -- 检查是否已存在相同judge_stove_no且differential不为空的记录
    declare
        v_count number;
    begin
        select count(*)
        into v_count
        from cx_ydm_zc_outlist t
        where judge_stove_no = curoporder.judge_stove_no
          and differential <> 0
          and bal_year_month = curoporder.bal_year_month;
        if v_count > 0 then
            insert into cx_ydm_zc_outlist
                (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,INSERT_TIME,DIFFERENTIAL,bak1)
              values
                (curoporder.bal_year_month, curoporder.ckmc, material_code_cx,
                 MATERIAL_NAME_cx, curoporder.spec_code,
                 outer_diam_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, gxstr,
                 curoporder.is_ckc, curoporder.dimater, curoporder.height,
                 curoporder.pass, curoporder.model_desc, curoporder.buyer,
                 curoporder.orderid, '成品出库', curoporder.judge_stove_no,sysdate,curoporder.lc,curoporder.outstock_type_name);
        else
            insert into cx_ydm_zc_outlist
                (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,INSERT_TIME,DIFFERENTIAL,bak1)
              values
                (curoporder.bal_year_month, curoporder.ckmc, material_code_cx,
                 MATERIAL_NAME_cx, curoporder.spec_code,
                 outer_diam_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, gxstr,
                 curoporder.is_ckc, curoporder.dimater, curoporder.height,
                 curoporder.pass, curoporder.model_desc, curoporder.buyer,
                 curoporder.orderid, '成品出库', curoporder.judge_stove_no,sysdate,curoporder.lc,curoporder.outstock_type_name);
        end if;
    end;
    else
      insert into cx_ydm_zc_outlist
        (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,DIFFERENTIAL,bak1)
      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, gxstr, curoporder.is_ckc, curoporder.dimater,
         curoporder.height, curoporder.pass, curoporder.model_desc,
         curoporder.buyer, curoporder.orderid, '成品出库',
         curoporder.judge_stove_no,curoporder.lc,curoporder.outstock_type_name);
    end if;
  
  end loop;
  close oporder_cursor;
  commit;

  /*
  
  exception
    when others then
      null;*/
end mes_cx_ydm_zc_outlist_new_hand;