set term ^;

create procedure TMP_ARCH_REL_RESTORE
as
  declare variable no_use varchar(255) ;
  declare variable def_vat integer;
  declare variable def_stock_group integer;
  declare variable del_id_stock integer;
  declare variable del_id_vat integer;
begin
  execute procedure SYSTEM_GET_VALUE (0, 'DEFAULT_VAT')
  returning_values :def_vat;

  select  first 1 sg.ID_STOCK_GROUP
  from    stock_group sg
  into    :def_stock_group;

  if (:def_stock_group is null) then exit;

  del_id_stock = null;
  select s.ID_STOCK
  from   STOCK s
  where  s.STOCK_NAME = '!!!deleted stock!!!'
  into   :del_id_stock;

  if (:del_id_stock is null) then begin
    execute procedure STOCK_MODIFY (
      null,
      '!!!deleted stock!!!',
      '!!!DELETED STOCK!!!',
      null,
      :def_stock_group,
      0,
      null,
      null,
      :def_vat,
      1,
      0,
      0,
      0,
      null,
      'add')
    returning_values (:del_id_stock, :no_use, :no_use, :no_use);
  end

  execute procedure NOM_MODIFY_VAT (null, null, '!DELETED!', 'X', 0, '', 'add')
  returning_values (:no_use, :no_use, :del_id_vat, :no_use);

  update ARCHIVE_LINE sl
  set    sl.ID_STOCK = :del_id_stock
  where  not exists (select s.ID_STOCK
                     from   STOCK s
                     where  s.ID_STOCK = sl.ID_STOCK);
  /*
  update ARCHIVE_LINE sl
  set    sl.ID_INVENT = :del_id_invent
  where  not exists (select i.ID_INVENT
                     from   INVENT i
                     where  i.ID_INVENT = sl.ID_INVENT);
  */
  update ARCHIVE_LINE sl
  set    sl.ID_VAT = :del_id_vat
  where  not exists (select v.ID_VAT
                     from   NOM_VAT_GROUP v
                     where  v.ID_VAT = sl.ID_VAT);

  update ARCHIVE_MODIFFIER sl
  set    sl.ID_STOCK = :del_id_stock
  where  not exists (select s.ID_STOCK
                     from   STOCK s
                     where  s.ID_STOCK = sl.ID_STOCK);

end
^


set term ;^

execute procedure TMP_ARCH_REL_RESTORE;

drop procedure TMP_ARCH_REL_RESTORE;


insert into NOM_DISCOUNT (ID_DISCOUNT, DISCOUNT_NAME, DISCOUNT_VALUE, ID_SECURE_LEVEL, IS_FREE_VALUE, MIN_VALUE,
                          MAX_VALUE, IS_USE, IS_PERCENT, IS_SURCHARGE)
select first 1 GEN_ID(GEN_NOM_DISCOUNT_ID, 1), '!!!DELETED!!!', 0,
       (select max(sl.ID_SECURE_LEVEL)
        from   RIGHT_SECURE_LEVEL sl
        where  sl.RELATED_TABLE = 'DISCOUNT'),
       0, null, null, 0, 1, 0
from   ARCHIVE_DISCOUNT d
where  not exists (select nd.ID_DISCOUNT
                   from   NOM_DISCOUNT nd
                   where  nd.ID_DISCOUNT = d.ID_DISCOUNT);

update ARCHIVE_DISCOUNT ad
set    ad.ID_DISCOUNT = coalesce((select d.ID_DISCOUNT
                                  from   NOM_DISCOUNT d
                                  where  d.ID_DISCOUNT = ad.ID_DISCOUNT), GEN_ID(GEN_NOM_DISCOUNT_ID,0));

commit work;

