Установка Единого Окна — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
Строка 1: Строка 1:
 
==Предварительная подготовка==
 
==Предварительная подготовка==
 
  1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г.
 
  1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г.
−
  2) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES;
+
  2) Обновляем клиента синхронизации до версии не ниже DistributeClient2.202 от декабря 2016г.
−
  3) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG;  
+
3) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES;
−
  4) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный;  
+
  4) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG;  
 +
  5) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный;  
  
 
==Подготовка серверной базы и скриптов для Единого Окна==
 
==Подготовка серверной базы и скриптов для Единого Окна==
 
  1) Обновляем скрипты синхронизации на сервере;
 
  1) Обновляем скрипты синхронизации на сервере;
 
  2) Блокируем синхронизацию (оставляем только g$tasks);
 
  2) Блокируем синхронизацию (оставляем только g$tasks);
−
  3) Готовим серверную базу [[тут будет статья про это]];
+
  3) Готовим серверую БД [[Подготовка серверной БД для перевода на Единое Окно]];
  
 
==Останавливаем торговлю в точках==
 
==Останавливаем торговлю в точках==
Строка 30: Строка 31:
 
and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);
 
and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);
 
</pre>
 
</pre>
 +
 +
==Обновляем структуру БД клиентов==
 +
<pre>
 +
CREATE EXCEPTION EX_WARES_ID_NOT_FOUND 'Не найдена позиция (wares)';
 +
 +
/*НОВЫЕ ПОЛЯ*/
 +
 +
ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
 +
ALTER TABLE WARES ADD WHASH DM_ID;
 +
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
 +
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
 +
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
 +
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$UUID DM_UUID_NULL;
 +
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$SRVUPDDT DM_DATETIME;
 +
 +
/*ТРИГГЕРЫ*/
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
 +
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
 +
AS
 +
declare variable cur_id type of DM_UUID;
 +
begin
 +
  if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.barcode is null) then
 +
    new.barcode='';
 +
  select first 1 d$uuid from wares where
 +
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d
 +
 +
$uuid<>new.d$uuid into :cur_id;
 +
  if (cur_id is null) then exit;
 +
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
SET SQL DIALECT 3;
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_BI_DISTR FOR WARES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  new.insertdt=current_timestamp;
 +
  new.PACKET = gen_id(gen_WARES_PACKET,1);
 +
  if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.id is null) then
 +
  begin
 +
    new.id=UUID_TO_CHAR(GEN_UUID());
 +
  end
 +
  new.d$uuid=new.id;
 +
  if (new.d$srvupddt is null) then
 +
  begin
 +
 +
    if ((select param_value from params where param_id = 'WARES_ALLOW_ADD') = '1') then --Режим создания нового товара (1-запрещено)
 +
      exception EX_WARES_ID_NOT_FOUND (select svalue from vals where id = new.name_id);
 +
 +
    new.g$profile_id = cast((select param_value from params where param_id = 'CODE_PROFILE') as dm_id);
 +
    new.d$srvupddt='2000-01-01';
 +
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
  end
 +
  if (new.l_id is null) then
 +
    new.l_id=GEN_ID(GEN_WARES_ID,1);
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_SNAME_BI0 FOR WARES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
new.sname = (select svalue from vals where id = new.name_id);
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
 +
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
 +
AS
 +
declare variable cur_id type of DM_UUID;
 +
begin
 +
  if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.barcode is null) then
 +
    new.barcode='';
 +
  select first 1 d$uuid from wares where
 +
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d
 +
 +
$uuid<>new.d$uuid into :cur_id;
 +
  if (cur_id is null) then exit;
 +
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_BU0 FOR WARES
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
  if (new.orig_code is null) then
 +
    new.orig_code='';
 +
  if (new.name_id <> old.name_id) then
 +
  begin
 +
    new.mgn_name = null;
 +
    new.mgn_id = 0;
 +
  end
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
  if (new.name_id <> old.name_id) then
 +
  begin
 +
    new.mgn_name = null;
 +
    new.mgn_id = 0;
 +
  end
 +
  new.sname = (select svalue from vals where id = new.name_id);
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_AU_DDA FOR WARES
 +
ACTIVE AFTER UPDATE POSITION 0
 +
AS
 +
declare variable w_sname DM_TEXT;
 +
declare variable w_sizg DM_TEXT;
 +
declare variable w_scountry DM_TEXT;
 +
begin
 +
  if ( (new.id = old.id) and
 +
  ((new.name_id <> old.name_id) or (new.izg_id <> old.izg_id)
 +
  or (new.country_id <> old.country_id) or (new.barcode <> old.barcode)) ) then
 +
  begin
 +
 +
    select svalue from vals where id = new.name_id into :w_sname;
 +
    select svalue from vals where id = new.izg_id into :w_sizg;
 +
    select svalue from vals where id = new.country_id into :w_scountry;
 +
 +
    update doc_detail_active set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
 +
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
 +
    where ware_id=new.id;
 +
 +
    update doc_detail_virtual set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
 +
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
 +
    where ware_id=new.id;
 +
 +
    update doc_detail_active_treb set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
 +
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
 +
    where ware_id=new.id;
 +
 +
  end
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER VALS_BD0 FOR VALS
 +
ACTIVE BEFORE DELETE POSITION 0
 +
AS
 +
begin
 +
  exception ex_wrong_oper  'попытка удаления наименования! ' || old.id;
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_DISTR FOR DOC_DETAIL_ACTIVE
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  if (new.d$uuid is null) then
 +
  begin
 +
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
    new.d$srvupddt='2000-01-01';
 +
  end
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER WARES_HASH_BUI0 FOR WARES
 +
ACTIVE BEFORE INSERT OR UPDATE POSITION 1900
 +
AS
 +
begin
 +
select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE,new.orig_code) into new.whash;
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
 +
 +
 +
 +
 +
/*ПРОЦЕДУРЫ*/
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_AUTO_WARESLINK (
 +
    DOC_ID DM_ID_NULL,
 +
    ACTIVE_ID DM_ID_NULL)
 +
as
 +
declare variable DDA_BARCODE DM_TEXT1024;
 +
declare variable DDA_WARE_ID DM_UUID_NULL;
 +
declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL;
 +
declare variable DDA_ORIG_IZGID DM_UUID_NULL;
 +
declare variable DDA_ORIG_NAMEID DM_UUID_NULL;
 +
declare variable DDA_COUNTRY_ID DM_UUID_NULL;
 +
declare variable DDA_IZG_ID DM_UUID_NULL;
 +
declare variable DDA_NAME_ID DM_UUID_NULL;
 +
declare variable DDA_Z_ID DM_UUID_NULL;
 +
declare variable DDA_SKLAD_ID DM_TEXT;
 +
declare variable DDALOG_BARCODE DM_TEXT1024;
 +
declare variable DDALOG_WARE_ID DM_UUID_NULL;
 +
declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL;
 +
declare variable DDALOG_ORIG_IZGID DM_UUID_NULL;
 +
declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL;
 +
declare variable DDALOG_COUNTRY_ID DM_UUID_NULL;
 +
declare variable DDALOG_IZG_ID DM_UUID_NULL;
 +
declare variable DDALOG_NAME_ID DM_UUID_NULL;
 +
declare variable DDALOG_Z_ID DM_UUID_NULL;
 +
declare variable DDALOG_SKLAD_ID DM_TEXT;
 +
declare variable DDALOG2_BARCODE DM_TEXT1024;
 +
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
 +
declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL;
 +
declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL;
 +
declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL;
 +
declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL;
 +
declare variable DDALOG2_IZG_ID DM_UUID_NULL;
 +
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
 +
declare variable DDALOG2_Z_ID DM_UUID_NULL;
 +
declare variable DDALOG2_SKLAD_ID DM_TEXT;
 +
declare variable DDALOG_SNAME DM_TEXT;
 +
declare variable DDALOG_SIZG DM_TEXT;
 +
declare variable DDALOG_SCOUNTRY DM_TEXT;
 +
declare variable DDA_SNAME DM_TEXT;
 +
declare variable DDA_SIZG DM_TEXT;
 +
declare variable DDA_SCOUNTRY DM_TEXT;
 +
begin
 +
 +
select bcode_izg, ware_id, sname, sizg, scountry,
 +
  (select val_id from PR_GETVAL_ID(dda.sname, 0, dda.part_type)) as name_id,
 +
  (select val_id from PR_GETVAL_ID(dda.sizg, 3, dda.part_type)) as izg_id,
 +
  (select val_id from PR_GETVAL_ID(dda.scountry, 2, dda.part_type)) as country_id,
 +
  (select val_id from PR_GETVAL_ID(dda.sorig_name, 1, dda.part_type)) as orig_name_id,
 +
  (select val_id from PR_GETVAL_ID(dda.sorig_izg, 6, dda.part_type)) as orgi_izg_id,
 +
  (select val_id from PR_GETVAL_ID(dda.sorig_country, 5, dda.part_type)) as orig_country_id,
 +
  Z_ID, SKLAD_ID
 +
from doc_detail_active dda where doc_id = :doc_id and id = :active_id
 +
into dda_barcode, dda_ware_id, dda_sname, dda_sizg, dda_scountry, dda_name_id, dda_izg_id, dda_country_id, dda_orig_nameid,
 +
      dda_orig_izgid, dda_orig_countryid, dda_z_id, dda_sklad_id;
 +
 +
select first 1 bcode_izg, ware_id, sname, sizg, scountry,
 +
  (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
 +
  (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
 +
  (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
 +
  (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
 +
  (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
 +
  (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
 +
  Z_ID, SKLAD_ID
 +
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
 +
  into ddalog_barcode, ddalog_ware_id, ddalog_sname, ddalog_sizg, ddalog_scountry, ddalog_name_id, ddalog_izg_id, ddalog_country_id,
 +
      ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;
 +
 +
if ( (ddalog_ware_id is null) or (ddalog_sname is null) or
 +
      ((dda_sname = ddalog_sname) and (dda_sizg = ddalog_sizg) and (dda_scountry = ddalog_scountry) and (dda_barcode = ddalog_barcode)) )  then exit;
 +
 +
 +
if ( (ddalog_ware_id = '0') or (ddalog_ware_id = '-1') ) then
 +
begin
 +
  if (not exists(select id from wares_log where id = :dda_ware_id and name_id = :ddalog_name_id and izg_id = :ddalog_izg_id
 +
      and country_id = :ddalog_country_id and barcode = :ddalog_barcode)) then
 +
      insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE, Z_ID, SKLAD_ID)
 +
      values (:dda_ware_id, :ddalog_name_id, :ddalog_izg_id, :ddalog_country_id, :ddalog_orig_nameid, :ddalog_orig_izgid,
 +
              :ddalog_orig_countryid, :ddalog_barcode, :ddalog_z_id, :ddalog_sklad_id);
 +
end
 +
 +
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_AUTO_WARESLINK;
 +
GRANT SELECT,INSERT ON WARES_LOG TO PROCEDURE PR_AUTO_WARESLINK;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_AUTO_WARESLINK TO SYSDBA;
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_NEW_PART (
 +
    DOC_ID type of DM_ID,
 +
    PARENT_ID type of DM_ID,
 +
    WARE_ID type of DM_UUID_NULL,
 +
    PRICE type of DM_DOUBLE,
 +
    PRICE_O type of DM_DOUBLE,
 +
    PRICE_Z type of DM_DOUBLE,
 +
    PRICE_R type of DM_DOUBLE,
 +
    QUANT type of DM_DOUBLE,
 +
    BARCODE type of DM_TEXT,
 +
    BARCODE1 type of DM_TEXT,
 +
    DEP type of DM_ID,
 +
    GODENDO type of DM_DATETIME,
 +
    SERIA type of DM_TEXT,
 +
    NDS type of DM_DOUBLE,
 +
    SUM_NDSO type of DM_DOUBLE,
 +
    SERT type of DM_TEXT,
 +
    DATESERT type of DM_DATETIME,
 +
    KEMVSERT type of DM_TEXT,
 +
    SDSERT type of DM_DATETIME,
 +
    REGN type of DM_TEXT,
 +
    NGTD type of DM_TEXT,
 +
    EDIZM type of DM_TEXT,
 +
    NAC type of DM_DOUBLE,
 +
    MOTHERPART_ID type of DM_ID,
 +
    PART_TYPE DM_STATUS,
 +
    BASE_AGENT_ID DM_ID,
 +
    SKLAD_ID type of DM_TEXT,
 +
    CONTRACT_ID DM_ID,
 +
    DOC_DETAIL_ACTIVE_ID DM_ID,
 +
    GROUP_ID DM_ID,
 +
    MOTHERPART_UUID DM_UUID_NULL = 0,
 +
    EGAIS_ID DM_TEXT1024 = null,
 +
    EGAIS_REGID DM_TEXT1024 = null,
 +
    EGAIS_BREGID DM_TEXT1024 = null,
 +
    EGAIS_ALCCODE DM_TEXT1024 = null,
 +
    EGAIS_QUANT DM_DOUBLE = 0,
 +
    CAPACITY DM_DOUBLE = 0,
 +
    ALC_VOLUME DM_DOUBLE = 0,
 +
    EGAIS_PRODUCER_ID DM_TEXT1024 = null,
 +
    EGAIS_BARCODE DM_TEXT1024 = null,
 +
    EGAIS_TYPE DM_TEXT = null,
 +
    PRODUCER_INN DM_TEXT1024 = null,
 +
    PRODUCER_KPP DM_TEXT1024 = null,
 +
    BOTTLINGDATE DM_DATETIME = null,
 +
    EGAIS_PRODUCT_VCODE DM_TEXT = null)
 +
returns (
 +
    P_ID type of DM_ID)
 +
as
 +
declare variable ORIG_SNAME DM_TEXT = 0;
 +
declare variable ORIG_SIZG DM_TEXT = 0;
 +
declare variable ORIG_SCOUNTRY DM_TEXT = 0;
 +
declare variable ORIG_BCODE_IZG DM_TEXT;
 +
begin
 +
 +
  select iif(trim(coalesce(sorig_name, '')) = '', sname, sorig_name),
 +
  iif(trim(coalesce(sorig_izg, '')) = '', sizg, sorig_izg),
 +
  iif(trim(coalesce(sorig_country, '')) = '', scountry, sorig_country),
 +
  bcode_izg
 +
  from doc_detail_active where id = :doc_detail_active_id into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg;
 +
 +
 +
  p_id=gen_id(gen_parts_id,1);
 +
  insert into parts
 +
   
 +
 +
(ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,D
 +
 +
ATESERT,KEMVSERT,SDSERT,REGN,NGTD,
 +
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
 +
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
 +
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE, ORIG_SNAME, ORIG_SIZG, ORIG_SCOUNTRY,
 +
 +
ORIG_BCODE_IZG)
 +
  values
 +
   
 +
 +
(:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_N
 +
 +
DSO,:SERT,:DATESERT,:KEMVSERT,
 +
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
 +
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID,
 +
 +
:EGAIS_BARCODE,
 +
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE, :ORIG_SNAME, :ORIG_SIZG, :ORIG_SCOUNTRY,
 +
 +
:ORIG_BCODE_IZG);
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_NEW_PART;
 +
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GETWARE_BY_IDS (
 +
    NAME_ID type of DM_UUID_NULL,
 +
    IZG_ID type of DM_UUID_NULL,
 +
    COUNTRY_ID type of DM_UUID_NULL,
 +
    ORIG_CODE type of DM_TEXT,
 +
    ORIG_NAME_ID type of DM_UUID_NULL,
 +
    ORIG_IZG_ID type of DM_UUID_NULL,
 +
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
 +
    DOINSERT type of DM_STATUS,
 +
    SEARCHINLOGS type of DM_STATUS,
 +
    BARCODE DM_TEXT)
 +
returns (
 +
    WARE_ID type of DM_UUID_NULL)
 +
as
 +
begin
 +
  if (ORIG_CODE is null) then
 +
    ORIG_CODE = '';
 +
  if (SEARCHINLOGS is null) then
 +
    SEARCHINLOGS = 0;
 +
  if (SEARCHINLOGS = 1) then
 +
  begin
 +
--    select iif(max(actual_ware_id) is null, max(id), max(actual_ware_id)) from WARES_LOG
 +
--    where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode
 +
--    into :WARE_ID;
 +
    select first 1 id from WARES_LOG
 +
      where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode order by log_insertdt desc
 +
    into :WARE_ID;
 +
    if (WARE_ID is not null) then
 +
      WARE_ID=coalesce((select first 1 actual_ware_id from WARES_LOG where id=:WARE_ID and actual_ware_id is not null order by log_insertdt desc),ware_id);
 +
    if (WARE_ID is not null) then
 +
        select id from wares where id = :WARE_ID into :WARE_ID;
 +
  end
 +
  else
 +
  begin
 +
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode
 +
    into :WARE_ID;
 +
  end
 +
  if ((WARE_ID is null) and
 +
      (DOINSERT = 1) and
 +
      (SEARCHINLOGS <> 1)) then
 +
  begin
 +
    --ware_id=gen_id(gen_wares_id,1);
 +
    WARE_ID = uuid_to_char(gen_uuid());
 +
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID,
 +
                      SKLAD_ID, BARCODE)
 +
    values (:WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, '',
 +
            :BARCODE);
 +
  end
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
 +
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GET_WARE (
 +
    SNAME type of DM_TEXT,
 +
    SIZG type of DM_TEXT,
 +
    SCOUNTRY type of DM_TEXT,
 +
    ORIG_CODE type of DM_TEXT = '',
 +
    SORIG_NAME type of DM_TEXT = '',
 +
    SORIG_IZG type of DM_TEXT = '',
 +
    SORIG_COUNTRY type of DM_TEXT = '',
 +
    BARCODE type of DM_TEXT = '',
 +
    Z_ID type of DM_ID = 0,
 +
    SKLAD_ID DM_TEXT = '',
 +
    ALTTYPE DM_STATUS = 0,
 +
    MNN DM_TEXT = '')
 +
returns (
 +
    W_ID type of DM_UUID_NULL)
 +
as
 +
declare variable NAME_ID type of DM_UUID_NULL;
 +
declare variable IZG_ID type of DM_UUID_NULL;
 +
declare variable COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
 +
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
 +
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
 +
begin
 +
  if (Z_ID is null) then Z_ID = 0;
 +
  select VAL_ID from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN) into :NAME_ID;
 +
  select VAL_ID from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE) into :IZG_ID;
 +
  select VAL_ID from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE) into :COUNTRY_ID;
 +
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
 +
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
 +
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
 +
  /*
 +
select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
 +
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
 +
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
 +
and barcode=:barcode into :w_id;
 +
*/
 +
 +
  if (exists(select RDB$INDEX_NAME from RDB$INDEX_SEGMENTS ris where ris.rdb$index_name='WARES_IDX_U' and ris.rdb$field_name = 'BARCODE')) then
 +
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into :W_ID;
 +
  else
 +
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID into :W_ID;
 +
--  select id from wares where whash = (select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE,:orig_code)) into :W_ID;
 +
  if (W_ID is null) then
 +
  begin
 +
    --    exception EX_WRONG_OPER;
 +
    --w_id=gen_id(gen_wares_id,1);
 +
    W_ID = uuid_to_char(gen_uuid());
 +
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID)
 +
    values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE, :Z_ID, :SKLAD_ID);
 +
  end
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
 +
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_MGNLINK (
 +
    VTYPE type of DM_STATUS,
 +
    RIGHT_ID type of DM_UUID_NULL,
 +
    WRONG_ID type of DM_UUID_NULL,
 +
    WITHBLOCKS type of DM_STATUS)
 +
as
 +
declare variable NAME_ID type of DM_UUID_NULL;
 +
declare variable COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable IZG_ID type of DM_UUID_NULL;
 +
declare variable W_NAME_ID type of DM_UUID_NULL;
 +
declare variable W_COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable W_IZG_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_CODE DM_TEXT;
 +
declare variable W_ORIG_NAME_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable W_ORIG_IZG_ID type of DM_UUID_NULL;
 +
declare variable ID DM_UUID_NULL;
 +
declare variable TNAME_ID type of DM_UUID_NULL;
 +
declare variable CHECK_ID DM_UUID_NULL;
 +
declare variable BARCODE DM_TEXT;
 +
begin
 +
select NAME_ID, COUNTRY_ID, IZG_ID from WARES where ID = :RIGHT_ID into :NAME_ID, :COUNTRY_ID, :IZG_ID;
 +
select NAME_ID, COUNTRY_ID, IZG_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_COUNTRY_ID, ORIG_IZG_ID, BARCODE from WARES where ID = :WRONG_ID
 +
into :W_NAME_ID, :W_COUNTRY_ID, :W_IZG_ID, :W_ORIG_CODE, :W_ORIG_NAME_ID, :W_ORIG_COUNTRY_ID, :W_ORIG_IZG_ID, :BARCODE;
 +
if ((NAME_ID is null) or (COUNTRY_ID is null) or (IZG_ID is null)) then
 +
exception EX_WARES_ID_NOT_FOUND;
 +
--  execute procedure pr_debug_wrongvals(:dbgsname,:name_id);
 +
--  execute procedure pr_debug_wrongvals(:dbgsizg,:izg_id);
 +
--  execute procedure pr_debug_wrongvals(:dbgscountry,:country_id);
 +
CHECK_ID = '0';
 +
if (vtype = 0) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:name_id, :w_izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
else if (vtype = 2) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :w_izg_id, :country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
else if (vtype = 3) then
 +
begin
 +
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0,
 +
 +
:BARCODE)
 +
into :CHECK_ID;
 +
end
 +
if (CHECK_ID <> '0') then
 +
begin
 +
update WARES set ORIG_CODE = substring(id || '@' || orig_code from 1 for 250)  where ID = :WRONG_ID;
 +
--update PARTS set WARE_ID = :right_id where WARE_ID = :WRONG_ID; --version 2: updating parts, but no actual link is created
 +
--exit;
 +
end
 +
if (VTYPE = 0) then
 +
begin
 +
select ID, NAME_ID from WARES where ID = :WRONG_ID into :ID, :TNAME_ID;
 +
if (WITHBLOCKS = 1) then
 +
begin
 +
update WARES set NAME_ID = :NAME_ID where ID = :ID or NAME_ID = :TNAME_ID;
 +
end
 +
else
 +
begin
 +
update WARES set NAME_ID = :NAME_ID where ID = :WRONG_ID;
 +
end
 +
end
 +
else
 +
if (VTYPE = 2) then
 +
begin
 +
--    select svalue from vals where id=:country_id into :tmp;
 +
--    if (tmp is null) then exception ex_svalue_not_found;
 +
update WARES set COUNTRY_ID = :COUNTRY_ID where ID = :WRONG_ID;
 +
--    update totalpricelist set country_id=:country_id, scountry=:tmp where system_id=:wrong_id;
 +
end
 +
else
 +
if (VTYPE = 3) then
 +
begin
 +
--    select svalue from vals where id=:izg_id into :tmp;
 +
--    if (tmp is null) then exception ex_svalue_not_found;
 +
update WARES set IZG_ID = :IZG_ID where ID = :WRONG_ID;
 +
--    update totalpricelist set izg_id=:izg_id, sizg=:tmp where system_id=:wrong_id;
 +
end
 +
else
 +
exception EX_UNKNOWN_MGNTYPE;
 +
for select PART_ID from WAREBASE where WARE_ID in (:WRONG_ID, :RIGHT_ID) into :ID
 +
do
 +
execute procedure PR_UPDBLOCKINFO(:ID);
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_MGNLINK;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MGNLINK;
 +
GRANT SELECT ON WAREBASE TO PROCEDURE PR_MGNLINK;
 +
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO TO PROCEDURE PR_MGNLINK;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO SYSDBA;
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_MAKEGOODWAREVALUES (
 +
    SNAME type of DM_TEXT,
 +
    SORIG_NAME type of DM_TEXT,
 +
    SIZG type of DM_TEXT,
 +
    SORIG_IZG type of DM_TEXT,
 +
    SCOUNTRY type of DM_TEXT,
 +
    SORIG_COUNTRY type of DM_TEXT,
 +
    ORIG_CODE type of DM_TEXT,
 +
    ACTIVE_ID type of DM_ID,
 +
    ALTTYPE DM_STATUS,
 +
    BARCODE DM_TEXT1024 = '')
 +
returns (
 +
    GOODNAME type of DM_TEXT,
 +
    GOODIZG type of DM_TEXT,
 +
    GOODCOUNTRY type of DM_TEXT,
 +
    GOODBARCODE DM_TEXT)
 +
as
 +
declare variable WARE_ID type of DM_UUID_NULL;
 +
declare variable NAME_ID type of DM_UUID_NULL;
 +
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
 +
declare variable IZG_ID type of DM_UUID_NULL;
 +
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
 +
declare variable COUNTRY_ID type of DM_UUID_NULL;
 +
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
 +
begin
 +
 +
  select id from vals where vtype=0 and alttype=:alttype and svalue=:sname into :name_id;
 +
  select id from vals where vtype=1 and alttype=:alttype and svalue=:sorig_name into :orig_name_id;
 +
  select id from vals where vtype=3 and alttype=:alttype and svalue=:sizg into :izg_id;
 +
  select id from vals where vtype=6 and alttype=:alttype and svalue=:sorig_izg into :orig_izg_id;
 +
  select id from vals where vtype=2 and alttype=:alttype and svalue=:scountry into :country_id;
 +
  select id from vals where vtype=5 and alttype=:alttype and svalue=:sorig_country into :orig_country_id;
 +
 +
 +
 +
  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,1, :barcode) into :ware_id;
 +
  if (ware_id is not null) then
 +
  begin
 +
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
 +
    select sname, sizg, scountry, barcode from VW_WARES where id=:ware_id into :goodname, :goodizg, :goodcountry, :goodbarcode;
 +
--    if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
 +
--      insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);
 +
    --goodname='2';--sname;
 +
  if (goodname is null)  then goodname = sname;
 +
  if (goodizg is null)  then goodizg = sizg;
 +
  if (goodcountry is null)  then goodcountry = scountry;
 +
  if (goodname is null)  then goodname = sorig_name;
 +
  if (goodizg is null)  then goodizg = sorig_izg;
 +
  if (goodcountry is null)  then goodcountry = sorig_country;
 +
  if (goodbarcode is null)  then goodbarcode = barcode;
 +
    suspend;
 +
    exit;
 +
  end
 +
 +
  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,null, :barcode) into :ware_id;
 +
  if (ware_id is not null) then
 +
  begin
 +
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
 +
    --goodname='1';--sname;
 +
    goodname=sname;
 +
    goodizg=sizg;
 +
    goodcountry=scountry;
 +
    goodbarcode=barcode;
 +
 +
    suspend;
 +
    exit;
 +
  end
 +
 +
 +
  ware_id=null;
 +
  if (orig_name_id is not null) then
 +
  begin
 +
    select first 1 id from wares where orig_name_id=:orig_name_id order by INSERTDT desc into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_name_id=:orig_name_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if ((ware_id is null) and (name_id is not null)) then
 +
  begin
 +
    select first 1 id from wares where name_id=:name_id into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if (ware_id is null) then
 +
  begin
 +
    --goodname='3';--sname;
 +
    goodname=sname;
 +
    name_id=null;
 +
  end
 +
  else
 +
  begin
 +
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
 +
    select w.name_id, v.svalue from WARES w left join vals v on w.name_id=v.id where w.id=:ware_id into :name_id, :goodname;
 +
  end
 +
 +
 +
  ware_id=null;
 +
  if (orig_izg_id is not null) then
 +
  begin
 +
    select first 1 id from wares where orig_izg_id=:orig_izg_id into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_izg_id=:orig_izg_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if ((ware_id is null) and (izg_id is not null)) then
 +
  begin
 +
    select first 1 id from wares where izg_id=:izg_id into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where izg_id=:izg_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if (ware_id is null) then
 +
    goodizg=sizg;
 +
  else
 +
  begin
 +
  --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
 +
    select w.izg_id, v.svalue from WARES w left join vals v on w.izg_id=v.id where w.id=:ware_id into :izg_id, :goodizg;
 +
  end
 +
 +
 +
  ware_id=null;
 +
  if (orig_country_id is not null) then
 +
  begin
 +
    select first 1 id from wares where orig_country_id=:orig_country_id into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_country_id=:orig_country_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if ((ware_id is null) and (country_id is not null)) then
 +
  begin
 +
    select first 1 id from wares where country_id=:country_id into :ware_id;
 +
    if (ware_id is null) then
 +
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where country_id=:country_id order by LOG_INSERTDT desc into :ware_id;
 +
  end
 +
  if (ware_id is null) then
 +
    goodcountry=scountry;
 +
  else
 +
  begin
 +
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
 +
    select w.country_id, v.svalue from WARES w left join vals v on w.country_id=v.id where w.id=:ware_id into :country_id, :goodcountry;
 +
  end
 +
 +
-- if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
 +
--  insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);
 +
 +
  --goodname='3';
 +
  goodbarcode=barcode;
 +
 +
  if (goodname is null)  then goodname = sname;
 +
  if (goodizg is null)  then goodizg = sizg;
 +
  if (goodcountry is null)  then goodcountry = scountry;
 +
  if (goodname is null)  then goodname = sorig_name;
 +
  if (goodizg is null)  then goodizg = sorig_izg;
 +
  if (goodcountry is null)  then goodcountry = sorig_country;
 +
 +
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT ON VALS TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON VW_WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO STANDART;
 +
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO SYSDBA;
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GET_WARES_HASH (
 +
    NAME_ID type of DM_UUID_NULL,
 +
    IZG_ID type of DM_UUID_NULL,
 +
    COUNTRY_ID type of DM_UUID_NULL,
 +
    BARCODE type of DM_TEXT,
 +
    ORIG_CODE type of DM_TEXT)
 +
returns (
 +
    WHASH DM_ID)
 +
as
 +
begin
 +
  if (exists(select RDB$INDEX_NAME from RDB$INDEX_SEGMENTS ris where ris.rdb$index_name='WARES_IDX_U' and ris.rdb$field_name = 'BARCODE')) then
 +
  whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:BARCODE, '')||coalesce
 +
 +
(:ORIG_CODE, ''));
 +
  else
 +
  whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:ORIG_CODE, ''));
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GETWARE_BY_IDS;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GET_WARE;
 +
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO SYSDBA;
 +
 +
 +
 +
 +
 +
/*ПРЕДСТАВЛЕНИЯ*/
 +
 +
 +
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Views                                  ***/
 +
/******************************************************************************/
 +
 +
 +
/* View: VW_WARES */
 +
CREATE OR ALTER VIEW VW_WARES(
 +
    ID,
 +
    NAME_ID,
 +
    SNAME,
 +
    IZG_ID,
 +
    SIZG,
 +
    COUNTRY_ID,
 +
    SCOUNTRY,
 +
    ORIG_CODE,
 +
    ORIG_NAME_ID,
 +
    SORIG_NAME,
 +
    ORIG_IZG_ID,
 +
    SORIG_IZG,
 +
    ORIG_COUNTRY_ID,
 +
    SORIG_COUNTRY,
 +
    BARCODE,
 +
    Z_ID,
 +
    SKLAD_ID,
 +
    INSERTDT,
 +
    PACKET,
 +
    MGN_NAME,
 +
    MGN_ID,
 +
    MGN_SOURCE)
 +
AS
 +
select
 +
w.ID,
 +
w.NAME_ID,
 +
w.sname,
 +
w.IZG_ID,
 +
(select svalue from vals where id = w.izg_id),
 +
w.COUNTRY_ID,
 +
(select svalue from vals where id = w.country_id),
 +
w.ORIG_CODE,
 +
w.ORIG_NAME_ID,
 +
(select svalue from vals where id = w.orig_name_id),
 +
w.ORIG_IZG_ID,
 +
(select svalue from vals where id = w.orig_izg_id),
 +
w.ORIG_COUNTRY_ID,
 +
(select svalue from vals where id = w.orig_country_id),
 +
w.BARCODE,
 +
w.Z_ID,
 +
w.SKLAD_ID,
 +
w.INSERTDT,
 +
w.PACKET,
 +
w.mgn_name,
 +
w.mgn_id,
 +
w.mgn_source
 +
from wares w order by sname, BARCODE
 +
;
 +
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 +
 +
/* Privileges of users */
 +
GRANT ALL ON VW_WARES TO STANDART WITH GRANT OPTION;
 +
 +
 +
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Views                                  ***/
 +
/******************************************************************************/
 +
 +
 +
/* View: VW_WARES_TREB */
 +
CREATE OR ALTER VIEW VW_WARES_TREB(
 +
    ID,
 +
    WARE_ID,
 +
    NAME_ID,
 +
    IZG_ID,
 +
    COUNTRY_ID,
 +
    ORIG_CODE,
 +
    ORIG_NAME_ID,
 +
    ORIG_IZG_ID,
 +
    ORIG_COUNTRY_ID,
 +
    Z_ID,
 +
    SNAME,
 +
    SIZG,
 +
    SCOUNTRY,
 +
    SORIG_NAME,
 +
    SORIG_IZG,
 +
    SORIG_COUNTRY,
 +
    SKLAD_ID,
 +
    PRICE,
 +
    QUANT,
 +
    SUMMA,
 +
    SUMMA_O,
 +
    PRICE_O,
 +
    PRICE_Z,
 +
    PRICE_R,
 +
    BARCODE,
 +
    BARCODE1,
 +
    GODENDO,
 +
    SERIA,
 +
    NDS,
 +
    SUM_NDSO,
 +
    SERT,
 +
    DATESERT,
 +
    KEMVSERT,
 +
    SDSERT,
 +
    REGN,
 +
    NGTD,
 +
    EDIZM,
 +
    INSERTDT,
 +
    BCODE_IZG,
 +
    DDA_UUID,
 +
    D$UUID,
 +
    D$SRVUPDDT,
 +
    CMT_STATUS,
 +
    CMT_DT,
 +
    CMT_USER,
 +
    CMT_PROFILE_ID,
 +
    PART_TYPE,
 +
    RQ_USER,
 +
    RQ_PROFILE_ID,
 +
    CMT_SNAME,
 +
    CMT_SIZG,
 +
    CMT_SCOUNTRY,
 +
    CMT_BARCODE,
 +
    RQ_SPROFILE_ID)
 +
AS
 +
select
 +
wt.ID,
 +
wt.WARE_ID,
 +
wt.NAME_ID,
 +
wt.IZG_ID,
 +
wt.COUNTRY_ID,
 +
wt.ORIG_CODE,
 +
wt.ORIG_NAME_ID,
 +
wt.ORIG_IZG_ID,
 +
wt.ORIG_COUNTRY_ID,
 +
wt.Z_ID,
 +
wt.SNAME,
 +
wt.SIZG,
 +
wt.SCOUNTRY,
 +
wt.SORIG_NAME,
 +
wt.SORIG_IZG,
 +
wt.SORIG_COUNTRY,
 +
wt.SKLAD_ID,
 +
wt.PRICE,
 +
wt.QUANT,
 +
wt.SUMMA,
 +
wt.SUMMA_O,
 +
wt.PRICE_O,
 +
wt.PRICE_Z,
 +
wt.PRICE_R,
 +
wt.BARCODE,
 +
wt.BARCODE1,
 +
wt.GODENDO,
 +
wt.SERIA,
 +
wt.NDS,
 +
wt.SUM_NDSO,
 +
wt.SERT,
 +
wt.DATESERT,
 +
wt.KEMVSERT,
 +
wt.SDSERT,
 +
wt.REGN,
 +
wt.NGTD,
 +
wt.EDIZM,
 +
wt.INSERTDT,
 +
wt.BCODE_IZG,
 +
wt.DDA_UUID,
 +
wt.D$UUID,
 +
wt.D$SRVUPDDT,
 +
wt.CMT_STATUS,
 +
wt.CMT_DT,
 +
wt.CMT_USER,
 +
wt.CMT_PROFILE_ID,
 +
wt.PART_TYPE,
 +
wt.RQ_USER,
 +
wt.RQ_PROFILE_ID,
 +
(select svalue from vals where id =
 +
w.name_id),
 +
(select svalue from vals where id = w.izg_id),
 +
(select svalue from vals where id = w.country_id),
 +
w.barcode,
 +
(select caption from g$profiles where id = wt.RQ_PROFILE_ID)
 +
from wares_treb wt
 +
left join wares w on wt.ware_id = w.id
 +
;
 +
 +
</pre>
 +
 +
==Подготавливаем товары к сжатию==

Версия 10:18, 14 января 2017

Предварительная подготовка

1) Обновляем у всех клиентов Менеджер до версии не ниже ManagerXP2_273_5 от декабря 2016г.
2) Обновляем клиента синхронизации до версии не ниже DistributeClient2.202 от декабря 2016г.
3) Настраиваем двустороннюю синхронизацию таблиц WARES_TREB и G$PROFILES;
4) Проверяем, что настроена двусторонняя синхронизация таблицы WARES_LOG; 
5) Проверяем, что у клиентов в PARAMS есть параметр CODE_PROFILE (Код профиля) и он корректный;

Подготовка серверной базы и скриптов для Единого Окна

1) Обновляем скрипты синхронизации на сервере;
2) Блокируем синхронизацию (оставляем только g$tasks);
3) Готовим серверую БД Подготовка серверной БД для перевода на Единое Окно;

Останавливаем торговлю в точках

CREATE EXCEPTION EX_DONT_WORK 'Вход в программу заблокирован. Идет обновление...';
SET TERM ^ ;
CREATE OR ALTER TRIGGER SESSIONS_DONT_WORK_BI0 FOR SESSIONS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  exception EX_DONT_WORK; --Вход в программу заблокирован. Идет обновление...
end
^
SET TERM ; ^
INSERT INTO Z$SERVICE (ID, TASK, STATUS, INSERTDT, STARTDT, ENDDT, TXTLOG) VALUES (999, 'POLITE_RESTORE', 0, current_timestamp, current_timestamp, current_timestamp, NULL);
update sessions s
set s.enddt=current_timestamp, s.endflag=0, s.endsession_id=s.id
where 1=1
and s.prog containing 'manager'
and s.endflag is null
and cast(s.startdt as dm_date) >= dateadd(-3 day to current_date);

Обновляем структуру БД клиентов

CREATE EXCEPTION EX_WARES_ID_NOT_FOUND 'Не найдена позиция (wares)';

/*НОВЫЕ ПОЛЯ*/

ALTER TABLE WARES ADD G$PROFILE_ID DM_ID_NULL;
ALTER TABLE WARES ADD WHASH DM_ID;
ALTER TABLE WARES_LOG ADD ACTUAL_WARE_ID DM_UUID_NULL;
ALTER TABLE PARTS ADD ORIG_SNAME DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SIZG DM_TEXT;
ALTER TABLE PARTS ADD ORIG_SCOUNTRY DM_TEXT;
ALTER TABLE PARTS ADD ORIG_BCODE_IZG DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$UUID DM_UUID_NULL;
ALTER TABLE DOC_DETAIL_ACTIVE ADD D$SRVUPDDT DM_DATETIME;

/*ТРИГГЕРЫ*/

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable cur_id type of DM_UUID;
begin
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.barcode is null) then
    new.barcode='';
  select first 1 d$uuid from wares where
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d

$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^


SET TERM ; ^

SET SQL DIALECT 3;

SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BI_DISTR FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.insertdt=current_timestamp;
  new.PACKET = gen_id(gen_WARES_PACKET,1);
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.id is null) then
  begin
    new.id=UUID_TO_CHAR(GEN_UUID());
  end
  new.d$uuid=new.id;
  if (new.d$srvupddt is null) then
  begin

    if ((select param_value from params where param_id = 'WARES_ALLOW_ADD') = '1') then --Режим создания нового товара (1-запрещено)
      exception EX_WARES_ID_NOT_FOUND (select svalue from vals where id = new.name_id);

    new.g$profile_id = cast((select param_value from params where param_id = 'CODE_PROFILE') as dm_id);
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WARES',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
  if (new.l_id is null) then
    new.l_id=GEN_ID(GEN_WARES_ID,1);
end
^


SET TERM ; ^



SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_SNAME_BI0 FOR WARES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.sname = (select svalue from vals where id = new.name_id);
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BIU_COWA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable cur_id type of DM_UUID;
begin
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.barcode is null) then
    new.barcode='';
  select first 1 d$uuid from wares where
    NAME_ID=new.name_id and IZG_ID=new.izg_id and COUNTRY_ID=new.COUNTRY_ID and BARCODE=new.BARCODE and orig_code=new.orig_code and d

$uuid<>new.d$uuid into :cur_id;
  if (cur_id is null) then exit;
  new.orig_code='>ddp;'||current_timestamp||';'||UUID_TO_CHAR(GEN_UUID());
end
^


SET TERM ; ^

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BU0 FOR WARES
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.orig_code is null) then
    new.orig_code='';
  if (new.name_id <> old.name_id) then
  begin
    new.mgn_name = null;
    new.mgn_id = 0;
  end
end
^


SET TERM ; ^





SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
ACTIVE BEFORE UPDATE POSITION 0
AS
 begin
  if (new.name_id <> old.name_id) then
  begin
    new.mgn_name = null;
    new.mgn_id = 0;
  end
  new.sname = (select svalue from vals where id = new.name_id);
 end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_AU_DDA FOR WARES
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable w_sname DM_TEXT;
declare variable w_sizg DM_TEXT;
declare variable w_scountry DM_TEXT;
begin
  if ( (new.id = old.id) and
   ((new.name_id <> old.name_id) or (new.izg_id <> old.izg_id)
   or (new.country_id <> old.country_id) or (new.barcode <> old.barcode)) ) then
  begin

    select svalue from vals where id = new.name_id into :w_sname;
    select svalue from vals where id = new.izg_id into :w_sizg;
    select svalue from vals where id = new.country_id into :w_scountry;

    update doc_detail_active set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

    update doc_detail_virtual set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

    update doc_detail_active_treb set sname = :w_sname, sizg = :w_sizg, scountry = :w_scountry,
    bcode_izg = new.barcode, name_id = new.name_id, izg_id = new.izg_id, country_id = new.country_id
    where ware_id=new.id;

  end
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER VALS_BD0 FOR VALS
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  exception ex_wrong_oper  'попытка удаления наименования! ' || old.id;
end
^


SET TERM ; ^


SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_DISTR FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
  end
end
^


SET TERM ; ^



SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_HASH_BUI0 FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 1900
AS
begin
 select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE,new.orig_code) into new.whash;
end
^


SET TERM ; ^







/*ПРОЦЕДУРЫ*/



SET TERM ^ ;

create or alter procedure PR_AUTO_WARESLINK (
    DOC_ID DM_ID_NULL,
    ACTIVE_ID DM_ID_NULL)
as
declare variable DDA_BARCODE DM_TEXT1024;
declare variable DDA_WARE_ID DM_UUID_NULL;
declare variable DDA_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDA_ORIG_IZGID DM_UUID_NULL;
declare variable DDA_ORIG_NAMEID DM_UUID_NULL;
declare variable DDA_COUNTRY_ID DM_UUID_NULL;
declare variable DDA_IZG_ID DM_UUID_NULL;
declare variable DDA_NAME_ID DM_UUID_NULL;
declare variable DDA_Z_ID DM_UUID_NULL;
declare variable DDA_SKLAD_ID DM_TEXT;
declare variable DDALOG_BARCODE DM_TEXT1024;
declare variable DDALOG_WARE_ID DM_UUID_NULL;
declare variable DDALOG_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG_IZG_ID DM_UUID_NULL;
declare variable DDALOG_NAME_ID DM_UUID_NULL;
declare variable DDALOG_Z_ID DM_UUID_NULL;
declare variable DDALOG_SKLAD_ID DM_TEXT;
declare variable DDALOG2_BARCODE DM_TEXT1024;
declare variable DDALOG2_WARE_ID DM_UUID_NULL;
declare variable DDALOG2_ORIG_COUNTRYID DM_UUID_NULL;
declare variable DDALOG2_ORIG_IZGID DM_UUID_NULL;
declare variable DDALOG2_ORIG_NAMEID DM_UUID_NULL;
declare variable DDALOG2_COUNTRY_ID DM_UUID_NULL;
declare variable DDALOG2_IZG_ID DM_UUID_NULL;
declare variable DDALOG2_NAME_ID DM_UUID_NULL;
declare variable DDALOG2_Z_ID DM_UUID_NULL;
declare variable DDALOG2_SKLAD_ID DM_TEXT;
declare variable DDALOG_SNAME DM_TEXT;
declare variable DDALOG_SIZG DM_TEXT;
declare variable DDALOG_SCOUNTRY DM_TEXT;
declare variable DDA_SNAME DM_TEXT;
declare variable DDA_SIZG DM_TEXT;
declare variable DDA_SCOUNTRY DM_TEXT;
begin

 select bcode_izg, ware_id, sname, sizg, scountry,
   (select val_id from PR_GETVAL_ID(dda.sname, 0, dda.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(dda.sizg, 3, dda.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(dda.scountry, 2, dda.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_name, 1, dda.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_izg, 6, dda.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(dda.sorig_country, 5, dda.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
 from doc_detail_active dda where doc_id = :doc_id and id = :active_id
 into dda_barcode, dda_ware_id, dda_sname, dda_sizg, dda_scountry, dda_name_id, dda_izg_id, dda_country_id, dda_orig_nameid,
      dda_orig_izgid, dda_orig_countryid, dda_z_id, dda_sklad_id;

 select first 1 bcode_izg, ware_id, sname, sizg, scountry,
   (select val_id from PR_GETVAL_ID(ddal.sname, 0, ddal.part_type)) as name_id,
   (select val_id from PR_GETVAL_ID(ddal.sizg, 3, ddal.part_type)) as izg_id,
   (select val_id from PR_GETVAL_ID(ddal.scountry, 2, ddal.part_type)) as country_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_name, 1, ddal.part_type)) as orig_name_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_izg, 6, ddal.part_type)) as orgi_izg_id,
   (select val_id from PR_GETVAL_ID(ddal.sorig_country, 5, ddal.part_type)) as orig_country_id,
   Z_ID, SKLAD_ID
  from doc_detail_active_log ddal where doc_id = :doc_id and id = :active_id order by log_id
  into ddalog_barcode, ddalog_ware_id, ddalog_sname, ddalog_sizg, ddalog_scountry, ddalog_name_id, ddalog_izg_id, ddalog_country_id,
       ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;

 if ( (ddalog_ware_id is null) or (ddalog_sname is null) or
      ((dda_sname = ddalog_sname) and (dda_sizg = ddalog_sizg) and (dda_scountry = ddalog_scountry) and (dda_barcode = ddalog_barcode)) )  then exit;


 if ( (ddalog_ware_id = '0') or (ddalog_ware_id = '-1') ) then
 begin
   if (not exists(select id from wares_log where id = :dda_ware_id and name_id = :ddalog_name_id and izg_id = :ddalog_izg_id
       and country_id = :ddalog_country_id and barcode = :ddalog_barcode)) then
       insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE, Z_ID, SKLAD_ID)
       values (:dda_ware_id, :ddalog_name_id, :ddalog_izg_id, :ddalog_country_id, :ddalog_orig_nameid, :ddalog_orig_izgid,
               :ddalog_orig_countryid, :ddalog_barcode, :ddalog_z_id, :ddalog_sklad_id);
 end


end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT ON DOC_DETAIL_ACTIVE_LOG TO PROCEDURE PR_AUTO_WARESLINK;
GRANT SELECT,INSERT ON WARES_LOG TO PROCEDURE PR_AUTO_WARESLINK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_AUTO_WARESLINK TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_NEW_PART (
    DOC_ID type of DM_ID,
    PARENT_ID type of DM_ID,
    WARE_ID type of DM_UUID_NULL,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    PRICE_Z type of DM_DOUBLE,
    PRICE_R type of DM_DOUBLE,
    QUANT type of DM_DOUBLE,
    BARCODE type of DM_TEXT,
    BARCODE1 type of DM_TEXT,
    DEP type of DM_ID,
    GODENDO type of DM_DATETIME,
    SERIA type of DM_TEXT,
    NDS type of DM_DOUBLE,
    SUM_NDSO type of DM_DOUBLE,
    SERT type of DM_TEXT,
    DATESERT type of DM_DATETIME,
    KEMVSERT type of DM_TEXT,
    SDSERT type of DM_DATETIME,
    REGN type of DM_TEXT,
    NGTD type of DM_TEXT,
    EDIZM type of DM_TEXT,
    NAC type of DM_DOUBLE,
    MOTHERPART_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    BASE_AGENT_ID DM_ID,
    SKLAD_ID type of DM_TEXT,
    CONTRACT_ID DM_ID,
    DOC_DETAIL_ACTIVE_ID DM_ID,
    GROUP_ID DM_ID,
    MOTHERPART_UUID DM_UUID_NULL = 0,
    EGAIS_ID DM_TEXT1024 = null,
    EGAIS_REGID DM_TEXT1024 = null,
    EGAIS_BREGID DM_TEXT1024 = null,
    EGAIS_ALCCODE DM_TEXT1024 = null,
    EGAIS_QUANT DM_DOUBLE = 0,
    CAPACITY DM_DOUBLE = 0,
    ALC_VOLUME DM_DOUBLE = 0,
    EGAIS_PRODUCER_ID DM_TEXT1024 = null,
    EGAIS_BARCODE DM_TEXT1024 = null,
    EGAIS_TYPE DM_TEXT = null,
    PRODUCER_INN DM_TEXT1024 = null,
    PRODUCER_KPP DM_TEXT1024 = null,
    BOTTLINGDATE DM_DATETIME = null,
    EGAIS_PRODUCT_VCODE DM_TEXT = null)
returns (
    P_ID type of DM_ID)
as
declare variable ORIG_SNAME DM_TEXT = 0;
declare variable ORIG_SIZG DM_TEXT = 0;
declare variable ORIG_SCOUNTRY DM_TEXT = 0;
declare variable ORIG_BCODE_IZG DM_TEXT;
begin

  select iif(trim(coalesce(sorig_name, '')) = '', sname, sorig_name),
  iif(trim(coalesce(sorig_izg, '')) = '', sizg, sorig_izg),
  iif(trim(coalesce(sorig_country, '')) = '', scountry, sorig_country),
  bcode_izg
  from doc_detail_active where id = :doc_detail_active_id into :orig_sname, :orig_sizg, :orig_scountry, :orig_bcode_izg;


  p_id=gen_id(gen_parts_id,1);
  insert into parts
    

(ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,D

ATESERT,KEMVSERT,SDSERT,REGN,NGTD,
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_BARCODE,
    EGAIS_TYPE, PRODUCER_INN, PRODUCER_KPP, BOTTLINGDATE, EGAIS_PRODUCT_VCODE, ORIG_SNAME, ORIG_SIZG, ORIG_SCOUNTRY, 

ORIG_BCODE_IZG)
  values
    

(:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_N

DSO,:SERT,:DATESERT,:KEMVSERT,
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, 

:EGAIS_BARCODE,
    :EGAIS_TYPE, :PRODUCER_INN, :PRODUCER_KPP, :BOTTLINGDATE, :EGAIS_PRODUCT_VCODE, :ORIG_SNAME, :ORIG_SIZG, :ORIG_SCOUNTRY, 

:ORIG_BCODE_IZG);
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_NEW_PART;
GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO SYSDBA;


SET TERM ^ ;

create or alter procedure PR_GETWARE_BY_IDS (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    ORIG_CODE type of DM_TEXT,
    ORIG_NAME_ID type of DM_UUID_NULL,
    ORIG_IZG_ID type of DM_UUID_NULL,
    ORIG_COUNTRY_ID type of DM_UUID_NULL,
    DOINSERT type of DM_STATUS,
    SEARCHINLOGS type of DM_STATUS,
    BARCODE DM_TEXT)
returns (
    WARE_ID type of DM_UUID_NULL)
as
begin
  if (ORIG_CODE is null) then
    ORIG_CODE = '';
  if (SEARCHINLOGS is null) then
    SEARCHINLOGS = 0;
  if (SEARCHINLOGS = 1) then
  begin
--    select iif(max(actual_ware_id) is null, max(id), max(actual_ware_id)) from WARES_LOG
--    where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode
--    into :WARE_ID;
    select first 1 id from WARES_LOG
      where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode order by log_insertdt desc
     into :WARE_ID;
    if (WARE_ID is not null) then
      WARE_ID=coalesce((select first 1 actual_ware_id from WARES_LOG where id=:WARE_ID and actual_ware_id is not null order by log_insertdt desc),ware_id);
    if (WARE_ID is not null) then
        select id from wares where id = :WARE_ID into :WARE_ID;
  end
  else
  begin
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and barcode = :barcode
    into :WARE_ID;
  end
  if ((WARE_ID is null) and
      (DOINSERT = 1) and
      (SEARCHINLOGS <> 1)) then
  begin
    --ware_id=gen_id(gen_wares_id,1);
    WARE_ID = uuid_to_char(gen_uuid());
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID,
                       SKLAD_ID, BARCODE)
    values (:WARE_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, 0, '',
            :BARCODE);
  end
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON WARES_LOG TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GETWARE_BY_IDS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_UPDATEWARE4PART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_GET_WARE (
    SNAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT = '',
    SORIG_NAME type of DM_TEXT = '',
    SORIG_IZG type of DM_TEXT = '',
    SORIG_COUNTRY type of DM_TEXT = '',
    BARCODE type of DM_TEXT = '',
    Z_ID type of DM_ID = 0,
    SKLAD_ID DM_TEXT = '',
    ALTTYPE DM_STATUS = 0,
    MNN DM_TEXT = '')
returns (
    W_ID type of DM_UUID_NULL)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin
  if (Z_ID is null) then Z_ID = 0;
  select VAL_ID from PR_GETVAL_ID(:SNAME, 0, :ALTTYPE, :MNN) into :NAME_ID;
  select VAL_ID from PR_GETVAL_ID(:SIZG, 3, :ALTTYPE) into :IZG_ID;
  select VAL_ID from PR_GETVAL_ID(:SCOUNTRY, 2, :ALTTYPE) into :COUNTRY_ID;
  select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
  select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
  select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
  /*
select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
and barcode=:barcode into :w_id;
*/

  if (exists(select RDB$INDEX_NAME from RDB$INDEX_SEGMENTS ris where ris.rdb$index_name='WARES_IDX_U' and ris.rdb$field_name = 'BARCODE')) then
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE into :W_ID;
  else
    select first 1 ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID into :W_ID;
--  select id from wares where whash = (select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE,:orig_code)) into :W_ID;
  if (W_ID is null) then
  begin
    --    exception EX_WRONG_OPER;
    --w_id=gen_id(gen_wares_id,1);
    W_ID = uuid_to_char(gen_uuid());
    insert into WARES (ID, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, BARCODE, Z_ID, SKLAD_ID)
    values (:W_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :ORIG_CODE, :ORIG_NAME_ID, :ORIG_IZG_ID, :ORIG_COUNTRY_ID, :BARCODE, :Z_ID, :SKLAD_ID);
  end
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;


SET TERM ^ ;

create or alter procedure PR_MGNLINK (
    VTYPE type of DM_STATUS,
    RIGHT_ID type of DM_UUID_NULL,
    WRONG_ID type of DM_UUID_NULL,
    WITHBLOCKS type of DM_STATUS)
as
declare variable NAME_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable W_NAME_ID type of DM_UUID_NULL;
declare variable W_COUNTRY_ID type of DM_UUID_NULL;
declare variable W_IZG_ID type of DM_UUID_NULL;
declare variable W_ORIG_CODE DM_TEXT;
declare variable W_ORIG_NAME_ID type of DM_UUID_NULL;
declare variable W_ORIG_COUNTRY_ID type of DM_UUID_NULL;
declare variable W_ORIG_IZG_ID type of DM_UUID_NULL;
declare variable ID DM_UUID_NULL;
declare variable TNAME_ID type of DM_UUID_NULL;
declare variable CHECK_ID DM_UUID_NULL;
declare variable BARCODE DM_TEXT;
begin
select NAME_ID, COUNTRY_ID, IZG_ID from WARES where ID = :RIGHT_ID into :NAME_ID, :COUNTRY_ID, :IZG_ID;
select NAME_ID, COUNTRY_ID, IZG_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_COUNTRY_ID, ORIG_IZG_ID, BARCODE from WARES where ID = :WRONG_ID
into :W_NAME_ID, :W_COUNTRY_ID, :W_IZG_ID, :W_ORIG_CODE, :W_ORIG_NAME_ID, :W_ORIG_COUNTRY_ID, :W_ORIG_IZG_ID, :BARCODE;
if ((NAME_ID is null) or (COUNTRY_ID is null) or (IZG_ID is null)) then
exception EX_WARES_ID_NOT_FOUND;
--  execute procedure pr_debug_wrongvals(:dbgsname,:name_id);
--  execute procedure pr_debug_wrongvals(:dbgsizg,:izg_id);
--  execute procedure pr_debug_wrongvals(:dbgscountry,:country_id);
CHECK_ID = '0';
if (vtype = 0) then
begin
select ware_id from PR_GETWARE_BY_IDS(:name_id, :w_izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
else if (vtype = 2) then
begin
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :w_izg_id, :country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
else if (vtype = 3) then
begin
select ware_id from PR_GETWARE_BY_IDS(:w_name_id, :izg_id, :w_country_id, :w_orig_code, :w_orig_name_id, :w_orig_izg_id, :w_orig_country_id, 0, 0, 

:BARCODE)
into :CHECK_ID;
end
if (CHECK_ID <> '0') then
begin
update WARES set ORIG_CODE = substring(id || '@' || orig_code from 1 for 250)  where ID = :WRONG_ID;
--update PARTS set WARE_ID = :right_id where WARE_ID = :WRONG_ID; --version 2: updating parts, but no actual link is created
--exit;
end
if (VTYPE = 0) then
begin
select ID, NAME_ID from WARES where ID = :WRONG_ID into :ID, :TNAME_ID;
if (WITHBLOCKS = 1) then
begin
update WARES set NAME_ID = :NAME_ID where ID = :ID or NAME_ID = :TNAME_ID;
end
else
begin
update WARES set NAME_ID = :NAME_ID where ID = :WRONG_ID;
end
end
else
if (VTYPE = 2) then
begin
--    select svalue from vals where id=:country_id into :tmp;
--    if (tmp is null) then exception ex_svalue_not_found;
update WARES set COUNTRY_ID = :COUNTRY_ID where ID = :WRONG_ID;
--    update totalpricelist set country_id=:country_id, scountry=:tmp where system_id=:wrong_id;
end
else
if (VTYPE = 3) then
begin
--    select svalue from vals where id=:izg_id into :tmp;
--    if (tmp is null) then exception ex_svalue_not_found;
update WARES set IZG_ID = :IZG_ID where ID = :WRONG_ID;
--    update totalpricelist set izg_id=:izg_id, sizg=:tmp where system_id=:wrong_id;
end
else
exception EX_UNKNOWN_MGNTYPE;
for select PART_ID from WAREBASE where WARE_ID in (:WRONG_ID, :RIGHT_ID) into :ID
do
execute procedure PR_UPDBLOCKINFO(:ID);
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,UPDATE ON WARES TO PROCEDURE PR_MGNLINK;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MGNLINK;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_MGNLINK;
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO TO PROCEDURE PR_MGNLINK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_MGNLINK TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_MAKEGOODWAREVALUES (
    SNAME type of DM_TEXT,
    SORIG_NAME type of DM_TEXT,
    SIZG type of DM_TEXT,
    SORIG_IZG type of DM_TEXT,
    SCOUNTRY type of DM_TEXT,
    SORIG_COUNTRY type of DM_TEXT,
    ORIG_CODE type of DM_TEXT,
    ACTIVE_ID type of DM_ID,
    ALTTYPE DM_STATUS,
    BARCODE DM_TEXT1024 = '')
returns (
    GOODNAME type of DM_TEXT,
    GOODIZG type of DM_TEXT,
    GOODCOUNTRY type of DM_TEXT,
    GOODBARCODE DM_TEXT)
as
declare variable WARE_ID type of DM_UUID_NULL;
declare variable NAME_ID type of DM_UUID_NULL;
declare variable ORIG_NAME_ID type of DM_UUID_NULL;
declare variable IZG_ID type of DM_UUID_NULL;
declare variable ORIG_IZG_ID type of DM_UUID_NULL;
declare variable COUNTRY_ID type of DM_UUID_NULL;
declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
begin

  select id from vals where vtype=0 and alttype=:alttype and svalue=:sname into :name_id;
  select id from vals where vtype=1 and alttype=:alttype and svalue=:sorig_name into :orig_name_id;
  select id from vals where vtype=3 and alttype=:alttype and svalue=:sizg into :izg_id;
  select id from vals where vtype=6 and alttype=:alttype and svalue=:sorig_izg into :orig_izg_id;
  select id from vals where vtype=2 and alttype=:alttype and svalue=:scountry into :country_id;
  select id from vals where vtype=5 and alttype=:alttype and svalue=:sorig_country into :orig_country_id;



  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,1, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select sname, sizg, scountry, barcode from VW_WARES where id=:ware_id into :goodname, :goodizg, :goodcountry, :goodbarcode;
--    if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
--      insert into doc_detail_active_log(id,sname,sizg,scountry,part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);
    --goodname='2';--sname;
  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;
  if (goodbarcode is null)  then goodbarcode = barcode;
    suspend;
    exit;
  end 

  select ware_id from PR_GETWARE_BY_IDS(:name_id,:izg_id,:country_id,:orig_code,:orig_name_id,:orig_izg_id,:orig_country_id,0,null, :barcode) into :ware_id;
  if (ware_id is not null) then
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    --goodname='1';--sname;
    goodname=sname;
    goodizg=sizg;
    goodcountry=scountry;
    goodbarcode=barcode;

    suspend;
    exit;
  end 


  ware_id=null;
  if (orig_name_id is not null) then
  begin
    select first 1 id from wares where orig_name_id=:orig_name_id order by INSERTDT desc into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_name_id=:orig_name_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (name_id is not null)) then
  begin
    select first 1 id from wares where name_id=:name_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where name_id=:name_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
  begin
    --goodname='3';--sname;
    goodname=sname;
    name_id=null;
  end
  else
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.name_id, v.svalue from WARES w left join vals v on w.name_id=v.id where w.id=:ware_id into :name_id, :goodname;
  end


  ware_id=null;
  if (orig_izg_id is not null) then
  begin
    select first 1 id from wares where orig_izg_id=:orig_izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_izg_id=:orig_izg_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (izg_id is not null)) then
  begin
    select first 1 id from wares where izg_id=:izg_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where izg_id=:izg_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
    goodizg=sizg;
  else
  begin
   --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.izg_id, v.svalue from WARES w left join vals v on w.izg_id=v.id where w.id=:ware_id into :izg_id, :goodizg;
  end


  ware_id=null;
  if (orig_country_id is not null) then
  begin
    select first 1 id from wares where orig_country_id=:orig_country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where orig_country_id=:orig_country_id order by LOG_INSERTDT desc into :ware_id;
  end
  if ((ware_id is null) and (country_id is not null)) then
  begin
    select first 1 id from wares where country_id=:country_id into :ware_id;
    if (ware_id is null) then
      select first 1 iif(actual_ware_id is null, id, actual_ware_id) from wares_log where country_id=:country_id order by LOG_INSERTDT desc into :ware_id;
  end
  if (ware_id is null) then
    goodcountry=scountry;
  else
  begin
    --select actualware_id from PR_GETACTUALWAREID(:ware_id) into :ware_id;
    select w.country_id, v.svalue from WARES w left join vals v on w.country_id=v.id where w.id=:ware_id into :country_id, :goodcountry;
  end

 -- if ((active_id>0) and ((goodname<>sname) or (goodizg<>sizg) or (goodcountry<>scountry))) then
 --   insert into doc_detail_active_log(id,sname,sizg,scountry, part_type) values (:active_id,:sname,:sizg,:scountry,:alttype);

  --goodname='3';
  goodbarcode=barcode;

  if (goodname is null)   then goodname = sname;
  if (goodizg is null)  then goodizg = sizg;
  if (goodcountry is null)  then goodcountry = scountry;
  if (goodname is null)   then goodname = sorig_name;
  if (goodizg is null)  then goodizg = sorig_izg;
  if (goodcountry is null)  then goodcountry = sorig_country;


  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON VALS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT EXECUTE ON PROCEDURE PR_GETWARE_BY_IDS TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON VW_WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES TO PROCEDURE PR_MAKEGOODWAREVALUES;
GRANT SELECT ON WARES_LOG TO PROCEDURE PR_MAKEGOODWAREVALUES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_MAKEGOODWAREVALUES TO SYSDBA;



SET TERM ^ ;

create or alter procedure PR_GET_WARES_HASH (
    NAME_ID type of DM_UUID_NULL,
    IZG_ID type of DM_UUID_NULL,
    COUNTRY_ID type of DM_UUID_NULL,
    BARCODE type of DM_TEXT,
    ORIG_CODE type of DM_TEXT)
returns (
    WHASH DM_ID)
as
begin
  if (exists(select RDB$INDEX_NAME from RDB$INDEX_SEGMENTS ris where ris.rdb$index_name='WARES_IDX_U' and ris.rdb$field_name = 'BARCODE')) then
   whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:BARCODE, '')||coalesce

(:ORIG_CODE, ''));
  else
   whash = hash(coalesce(:NAME_ID,'NAME_ID')||coalesce(:IZG_ID,'IZG_ID')||coalesce(:COUNTRY_ID,'COUNTRY_ID')||coalesce(:ORIG_CODE, ''));
  suspend;
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GETWARE_BY_IDS;
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARES_HASH TO SYSDBA;





/*ПРЕДСТАВЛЕНИЯ*/



/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_WARES */
CREATE OR ALTER VIEW VW_WARES(
    ID,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    COUNTRY_ID,
    SCOUNTRY,
    ORIG_CODE,
    ORIG_NAME_ID,
    SORIG_NAME,
    ORIG_IZG_ID,
    SORIG_IZG,
    ORIG_COUNTRY_ID,
    SORIG_COUNTRY,
    BARCODE,
    Z_ID,
    SKLAD_ID,
    INSERTDT,
    PACKET,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE)
AS
select
w.ID,
w.NAME_ID,
w.sname,
w.IZG_ID,
(select svalue from vals where id = w.izg_id),
w.COUNTRY_ID,
(select svalue from vals where id = w.country_id),
w.ORIG_CODE,
w.ORIG_NAME_ID,
(select svalue from vals where id = w.orig_name_id),
w.ORIG_IZG_ID,
(select svalue from vals where id = w.orig_izg_id),
w.ORIG_COUNTRY_ID,
(select svalue from vals where id = w.orig_country_id),
w.BARCODE,
w.Z_ID,
w.SKLAD_ID,
w.INSERTDT,
w.PACKET,
w.mgn_name,
w.mgn_id,
w.mgn_source
from wares w order by sname, BARCODE
;




/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


/* Privileges of users */
GRANT ALL ON VW_WARES TO STANDART WITH GRANT OPTION;



/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_WARES_TREB */
CREATE OR ALTER VIEW VW_WARES_TREB(
    ID,
    WARE_ID,
    NAME_ID,
    IZG_ID,
    COUNTRY_ID,
    ORIG_CODE,
    ORIG_NAME_ID,
    ORIG_IZG_ID,
    ORIG_COUNTRY_ID,
    Z_ID,
    SNAME,
    SIZG,
    SCOUNTRY,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    SKLAD_ID,
    PRICE,
    QUANT,
    SUMMA,
    SUMMA_O,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    GODENDO,
    SERIA,
    NDS,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    INSERTDT,
    BCODE_IZG,
    DDA_UUID,
    D$UUID,
    D$SRVUPDDT,
    CMT_STATUS,
    CMT_DT,
    CMT_USER,
    CMT_PROFILE_ID,
    PART_TYPE,
    RQ_USER,
    RQ_PROFILE_ID,
    CMT_SNAME,
    CMT_SIZG,
    CMT_SCOUNTRY,
    CMT_BARCODE,
    RQ_SPROFILE_ID)
AS
select
wt.ID,
wt.WARE_ID,
wt.NAME_ID,
wt.IZG_ID,
wt.COUNTRY_ID,
wt.ORIG_CODE,
wt.ORIG_NAME_ID,
wt.ORIG_IZG_ID,
wt.ORIG_COUNTRY_ID,
wt.Z_ID,
wt.SNAME,
wt.SIZG,
wt.SCOUNTRY,
wt.SORIG_NAME,
wt.SORIG_IZG,
wt.SORIG_COUNTRY,
wt.SKLAD_ID,
wt.PRICE,
wt.QUANT,
wt.SUMMA,
wt.SUMMA_O,
wt.PRICE_O,
wt.PRICE_Z,
wt.PRICE_R,
wt.BARCODE,
wt.BARCODE1,
wt.GODENDO,
wt.SERIA,
wt.NDS,
wt.SUM_NDSO,
wt.SERT,
wt.DATESERT,
wt.KEMVSERT,
wt.SDSERT,
wt.REGN,
wt.NGTD,
wt.EDIZM,
wt.INSERTDT,
wt.BCODE_IZG,
wt.DDA_UUID,
wt.D$UUID,
wt.D$SRVUPDDT,
wt.CMT_STATUS,
wt.CMT_DT,
wt.CMT_USER,
wt.CMT_PROFILE_ID,
wt.PART_TYPE,
wt.RQ_USER,
wt.RQ_PROFILE_ID,
(select svalue from vals where id = 
w.name_id),
(select svalue from vals where id = w.izg_id),
(select svalue from vals where id = w.country_id),
w.barcode,
(select caption from g$profiles where id = wt.RQ_PROFILE_ID)
from wares_treb wt
left join wares w on wt.ware_id = w.id
;

Подготавливаем товары к сжатию

Источник — «http://wiki.standart-n.ru/index.php?title=Установка_Единого_Окна&oldid=5283»