gistfile1.sql

-------------------------------------------
-------------------------------------------
---!!!Сначала проверьте на тестовой базе!!!
-------------------------------------------
-------------------------------------------

declare
  -----------------------------------  
  material_no             varchar(10) := '400706414'; -- номер выходного пакета
  defect_pcs              number := 1; -- количество брака  
  defect_code             number := 757; -- вид дефекта select * from description_value where upper(name) like '%КРИВЫЕ%'
  new_material_no         varchar(10) := '400706224'; -- номер пакета брака, опционально
  workshop                number := 4; -- Номер цеха, нужен для генерации кода
  lower_material_no       number := workshop * 100000000; -- Наименьший возможный код пакета
  greatest_material_no    number := lower_material_no + 99999999; -- Наибольшый возможный код пакета
  outlet_craddle_code     number := 433103; -- выходной карман линии
  defect_craddle_code     number := 432133; -- карман брака линни
  warehouse_id            number := 238214; -- ID склада
  tech_oper_id            number := 99; -- id тех. операции
  order_fact_calc_rule_id number := 64; -- id правила рассчета фактических значений
  defect_reason_code      number := 1; -- 1-по работе; 2-по металлу; 5-дефекты геометрических размеров;
  defect_type_code        number := 2; -- 1-лом; 2-исправимый; 3-окончательный;
  -----------------------------------  
  good_material     material%rowtype;
  parent_material   material%rowtype;
  added_material_id number;
  defect_row        description_value%rowtype;

  pcs number;
  tns number;
  mts number;

  quality_id number;
  lot_id     number;
  melt_id    number;

  shift_date    date;
  shift_no      number;
  shift_brigade varchar(1);

begin
  -- ищем брак
  begin
    select *
      into defect_row
      from description_value dv
     where dv.description_type_id = 12
       and dv.code = defect_code;
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20005,
                                'Вид дефекта не найден');
        rollback;
      end;
  end;

  dbms_output.put_line('Вид дефекта: ' || defect_row.name);

  -- ищем годный пакет
  begin
    select *
      into good_material
      from material m
     where m.no = material_no
       and exists
     (select 1
        from material_route_step mrs
       where mrs.material_id = m.material_id
         and mrs.operation_id =
                   (select operation_id from operation where code = 100) -- анонсирование
               and mrs.unit_id =
                   (select unit_id from unit where code = outlet_craddle_code)); -- выходной карман ТО
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20000,
                                'Не найден пакет, проходивший через выходной карман линии');
        rollback;
      end;
  end;

  -- ищем родителя
  begin
    select m.*
      into parent_material
      from material_relation mr, material m
     where mr.child_material_id = good_material.material_id
       and mr.parent_material_id = m.material_id;
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20001,
                                'Не найден родительский материал для пакета');
        rollback;
      end;
  end;

  -- рассчитаем количество брака, либо возьмем с последней операции корректировки, если пользователь уже исправил годный на складе
  pcs := defect_pcs;
  if good_material.pcs = parent_material.pcs - defect_pcs then
    tns := nvl(parent_material.tns, parent_material.proportional_tns) -
           nvl(good_material.tns, good_material.proportional_tns);
    mts := nvl(parent_material.mts, parent_material.proportional_mts) -
           nvl(good_material.mts, good_material.proportional_mts);
  else
    tns := round(nvl(parent_material.tns, parent_material.proportional_tns) /
                 parent_material.pcs * defect_pcs,
                 3);
    mts := round(nvl(parent_material.mts, parent_material.proportional_mts) /
                 parent_material.pcs * defect_pcs,
                 2);
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
-- ПРОКЛЯТОЕ МЕСТО ---
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
    -- Good material future values
    dbms_output.put_line(good_material.material_id || ' ' || to_char(nvl(good_material.tns, good_material.proportional_tns)) || ' ' || to_char(nvl(good_material.mts, good_material.proportional_mts)) || ' ' ||  to_char(nvl(good_material.pcs, good_material.proportional_pcs)));
    -- Good material future value
    dbms_output.put_line(parent_material.material_id || ' ' || to_char(nvl(parent_material.tns, parent_material.proportional_tns) - tns) || ' ' || to_char(nvl(parent_material.mts, parent_material.proportional_mts) - mts) || ' ' ||  to_char(nvl(parent_material.pcs, parent_material.proportional_pcs) - pcs));
    
    -- обновляем годный пакет
    update material m
       set m.tns = nvl(parent_material.tns, parent_material.proportional_tns) - tns,
           m.mts = nvl(parent_material.mts, parent_material.proportional_mts) - mts,
           m.pcs = nvl(parent_material.pcs, parent_material.proportional_pcs) - pcs
     where m.material_id = good_material.material_id;
  
    select *
      into good_material
      from material m
     where m.no = material_no
       and exists
     (select 1
              from material_route_step mrs
             where mrs.material_id = m.material_id
               and mrs.operation_id =
                   (select operation_id from operation where code = 100) -- анонсирование
               and mrs.unit_id =
                   (select unit_id from unit where code = outlet_craddle_code)); -- выходной карман ТО
     -- Good material new current value
     dbms_output.put_line(good_material.material_id || ' ' || to_char(nvl(good_material.tns, good_material.proportional_tns)) || ' ' || to_char(nvl(good_material.mts, good_material.proportional_mts)) || ' ' ||  to_char(nvl(good_material.pcs, good_material.proportional_pcs)));
  end if;
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
-- ПРОКЛЯТОЕ МЕСТО ---
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------
---------------------------------------------------------------

  -- обновляем шаги маршрута годного пакета
  update material_route_step mrs
     set mrs.pcs    = good_material.pcs,
         result_pcs = good_material.pcs,
         mrs.mts    = good_material.mts,
         result_mts = good_material.mts,
         mrs.tns    = good_material.tns,
         result_tns = good_material.tns
   where material_id = good_material.material_id
     and mrs.pcs is not null;
  -- обновляем строки годного пакета
  update document_line dl
     set dl.pcs = good_material.pcs,
         dl.tns = good_material.tns,
         dl.mts = good_material.mts
   where material_id = good_material.material_id
     and dl.pcs is not null;
  -- исправим связку с парентом годного пакета
  update material_relation
     set pcs = good_material.pcs,
         tns = good_material.tns,
         mts = good_material.mts
   where parent_material_id = parent_material.material_id
     and child_material_id = good_material.material_id;

  -- подберем код
  if trim(new_material_no) is null then
    select max(m1.no - 1)
      into new_material_no
      from material m1
     where m1.no > lower_material_no
       and m1.no < greatest_material_no
       and m1.no - 1 not in
           (select no
              from material
             where no > to_char(lower_material_no)
               and no < to_char(greatest_material_no));
  end if;

  dbms_output.put_line('Код нового пакета брака: ' || new_material_no);

  -- создаем аналогичный пакет брака
  insert into material
    (material_type_id, material_purpose_id,  no, name, tns, mts, pcs, theoretic_tns, theoretic_mts, theoretic_pcs, status, unit_id, prod_order_id, state)
  values
    (good_material.material_type_id,
     (case defect_type_code 
        when 2 -- исправимый брак
          then good_material.material_purpose_id
        else -- окончательный брак || лом
          (select mp.material_purpose_id from material_purpose mp where mp.code = 41) -- Брак
      end),
     new_material_no, good_material.name, tns, mts, pcs,  tns, mts, pcs, 'Stored', warehouse_id, parent_material.prod_order_id, 'None') -- на склад
  returning material_id into added_material_id;

  dbms_output.put_line('Добавлен пакет брака, Id:' || To_Char(added_material_id));
  dbms_output.put_line('Штук:' || To_Char(pcs));
  dbms_output.put_line('Метров:' || To_Char(mts));
  dbms_output.put_line('Тонн:' || To_Char(tns));

  -- копирование примечаний
  insert into material_desc
    (material_id, description_type_id, f_equal, description_value_id, min_value, max_value, begin_date, end_date, string_value, f_default, f_editable)
    (select added_material_id, description_type_id, f_equal, description_value_id, min_value, max_value, begin_date, end_date, string_value, f_default, f_editable
       from material_desc
      where material_id = good_material.material_id);

  -- добавление брака
  -- отбор образцов
  insert into material_desc
    (material_id, description_type_id, f_equal, description_value_id, min_value, f_default, f_editable)
  values
    (added_material_id,
     (select dt.description_type_id
        from description_type dt
       where dt.code = 187), --desc_type вид дефекта
     1, defect_row.description_value_id, pcs, 1, 0);

  -- исправимый брак   
  insert into material_desc
    (material_id, description_type_id, f_equal, description_value_id, min_value, f_default, f_editable)
  values
    (added_material_id,
     (select dt.description_type_id
        from description_type dt
       where dt.code = 190), --desc_type брак
     1,
     (select dv.description_value_id
        from description_value dv
       where dv.code = defect_type_code
         and dv.description_type_id in
             (select dt.description_type_id
                from description_type dt
               where dt.code = 190)), 
     pcs, 1, 0);

  -- брак по работе
  insert into material_desc
    (material_id, description_type_id, f_equal, description_value_id, min_value, f_default, f_editable)
  values
    (added_material_id,
     (select dt.description_type_id
        from description_type dt
       where dt.code = 210), -- desc_type причина дефекта
     1,
     (select dv.description_value_id
        from description_value dv
       where dv.code = defect_reason_code
         and dv.description_type_id in
             (select dt.description_type_id
                from description_type dt
               where dt.code = 210)), 
     pcs,
     1, 0);

  -- добавление связки с парентом
  insert into material_relation
    (parent_material_id, child_material_id, tns, mts, pcs)
  values
    (parent_material.material_id, added_material_id, tns, mts, pcs);

  -- добавление строки в ведомость качества
  begin
    select d.document_id
      into quality_id
      from documents d, document_line dl
     where d.document_id = dl.document_id
       and dl.material_id = good_material.material_id
       and d.document_type_id in (select dt.document_type_id
                                    from document_type dt
                                   where dt.code = 9); --ВК      
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20002,
                                'Не найдена ведомость качества для пакета');
        rollback;
      end;
  end;

  insert into document_line
    (document_id, material_id, no, tns, mts, pcs, status, type)
  values
    (quality_id, added_material_id,
     (select max(no) + 1 from document_line where document_id = quality_id),
     tns, mts, pcs, 'Created', 'Material');

  -- добавление строки в партию
  begin
    select d.document_id
      into lot_id
      from documents d, document_line dl
     where d.document_id = dl.document_id
       and dl.material_id = good_material.material_id
       and d.document_type_id in
           (select dt.document_type_id
              from document_type dt
             where dt.code = 18); -- журнал решистрации партий
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20003,
                                'Не найдена партия для пакета');
        rollback;
      end;
  end;

  insert into document_line
    (document_id, material_id, no, tns, mts, pcs, status, type)
  values
    (lot_id,
     added_material_id,
     (select max(no) + 1 from document_line where document_id = lot_id),
     tns, mts, pcs, 'Created', 'Material');

  -- добавление строки в плавку
  begin
    select d.document_id
      into melt_id
      from documents d, document_line dl
     where d.document_id = dl.document_id
       and dl.material_id = good_material.material_id
       and d.document_type_id in
           (select dt.document_type_id
              from document_type dt
             where dt.code = 23); -- плавка    
  exception
    when NO_DATA_FOUND then
      begin
        RAISE_APPLICATION_ERROR(-20004,
                                'Не найдена плавка для пакета');
        rollback;
      end;
  end;

  insert into document_line
    (document_id, material_id, no, tns, mts, pcs, status, type)
  values
    (melt_id,
     added_material_id,
     (select max(no) + 1 from document_line where document_id = melt_id),
     tns, mts, pcs, 'Created', 'Material');

  -- пересчитаем шапки
  for rec in (select *
                from document_line
               where material_id in
                     (added_material_id, good_material.material_id)) loop
    update documents d
       set d.tns =
           (select sum(tns)
              from document_line
             where document_id = d.document_id
               and status not in ('Annuled', 'Archived'))
     where document_id = rec.document_id;
    update documents d
       set d.mts =
           (select sum(mts)
              from document_line
             where document_id = d.document_id
               and status not in ('Annuled', 'Archived'))
     where document_id = rec.document_id;
    update documents d
       set d.pcs =
           (select sum(pcs)
              from document_line
             where document_id = d.document_id
               and status not in ('Annuled', 'Archived'))
     where document_id = rec.document_id;
  end loop;

  -- маршрут материала

  -- выберем смену создания годного пакета

  select mrs.shift_date, mrs.shift_no, mrs.shift_brigade
    into shift_date, shift_no, shift_brigade
    from material_route_step mrs
   where mrs.material_id = good_material.material_id
     and mrs.operation_id in
         (select o.operation_id from operation o where o.code = 110); -- Creation

  dbms_output.put_line('Смена создания:' || To_Char(shift_date) || ' ' ||
                       To_Char(shift_brigade) || ' ' || To_Char(shift_no));

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts,
     pcs, theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 1, (select unit_id from unit where code = defect_craddle_code), 'Production', (select o.operation_id from operation o where o.code = 100),
     sysdate, shift_brigade, shift_no, shift_date, null, parent_material.prod_order_id, null, null, null, null, null, null, null, null, null, null, null); -- анонсирование

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts,
     pcs, theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 2, (select unit_id from unit where code = defect_craddle_code), 'Registration', null, sysdate, shift_brigade, shift_no, shift_date, lot_id,
     parent_material.prod_order_id, 
     (select o.operation_id
        from operation o
       where lower(o.type) like '%technological%'
         and o.code = 515), null, 
     null, null, null, null, null, null, null, null, null); -- добавление партии

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts, pcs,
     theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 3, (select unit_id from unit where code = defect_craddle_code), 'Registration', null, sysdate, shift_brigade, shift_no, shift_date,  melt_id,
     parent_material.prod_order_id, 
     (select o.operation_id
        from operation o
       where lower(o.type) like '%technological%'
         and o.code = 515), 
    null, null, null, null, null, null, null, null, null, null); -- добавление плавки

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts, pcs, 
     theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 4, (select unit_id from unit where code = defect_craddle_code), 'Production', (select o.operation_id from operation o where o.code = 110),
     sysdate, shift_brigade, shift_no, shift_date, quality_id, parent_material.prod_order_id, tech_oper_id, tns, mts, pcs, tns, mts, pcs, order_fact_calc_rule_id,
     tns, mts, pcs); -- создание

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts, pcs,
     theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 5, (select unit_id from unit where code = defect_craddle_code), 'Production', null, sysdate, shift_brigade, shift_no, shift_date, quality_id,
     parent_material.prod_order_id,
     (select o.operation_id
        from operation o
       where lower(o.type) like '%technological%'
         and o.code = 515),
     tns, mts, pcs, tns, mts, pcs, null, tns, mts, pcs); -- добавление ведомости качества

  insert into material_route_step
    (material_id, no, unit_id, type, operation_id, operation_date, shift_brigade, shift_no, shift_date, document_id, prod_order_id, tech_operation_id, tns, mts, pcs,
     theoretic_tns, theoretic_mts, theoretic_pcs, order_fact_calc_rule_id, result_tns, result_mts, result_pcs)
  values
    (added_material_id, 6, (select unit_id from unit where unit_id = warehouse_id), 'Production', (select o.operation_id from operation o where o.code = 130), sysdate,
     shift_brigade, shift_no, shift_date, null, parent_material.prod_order_id, null, tns, mts, pcs, tns, mts, pcs, null, tns, mts, pcs); -- перемещение

end;


--select * from material m where m.material_id = 13143650