Подготовка серверной базы и скриптов для Единого Окна
Материал из wiki.standart-n.ru
Версия от 15:05, 6 сентября 2016; Aleksnick (обсуждение | вклад)
Содержание
- 1 Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert
- 2 Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)
- 3 Выпоняем cowa_all.sql (не оптимизирован для ЙО)
- 4 Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)
- 5 Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log
- 6 Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем
- 7 Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем
- 8 Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта
Экспорт vals_link (для ЙО: boss:d:\обмен\JO\ZTRADE.FDB) как dbf или insert
Создаем и вставляем данные vals_link в нашу БД (скрипт создания и импорта из dbf: cowa_vals_links.sql)
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_VALS_LINKS_ID;
CREATE TABLE VALS_LINKS (
ID DM_ID NOT NULL,
RIGHT_VAL_ID DM_UUID,
WRONG_VAL_ID DM_UUID,
INSERTDT DM_DATETIME,
VTYPE DM_STATUS NOT NULL,
SRIGHT_VAL DM_TEXT,
SWRONG_VAL DM_TEXT,
DELETEDT DM_DATETIME
);
/******************************************************************************/
/*** Unique Constraints ***/
/******************************************************************************/
ALTER TABLE VALS_LINKS ADD CONSTRAINT UNQ1_VALS_LINKS UNIQUE (RIGHT_VAL_ID, WRONG_VAL_ID);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE VALS_LINKS ADD CONSTRAINT PK_VALS_LINKS PRIMARY KEY (ID);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX VALS_LINKS_IDX1 ON VALS_LINKS (RIGHT_VAL_ID);
CREATE INDEX VALS_LINKS_IDX2 ON VALS_LINKS (WRONG_VAL_ID);
CREATE INDEX VALS_LINKS_IDX3 ON VALS_LINKS (SRIGHT_VAL, SWRONG_VAL);
CREATE INDEX VALS_LINKS_IDX4 ON VALS_LINKS (SRIGHT_VAL);
CREATE INDEX VALS_LINKS_IDX5 ON VALS_LINKS (SWRONG_VAL);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: VALS_LINKS_BI */
CREATE OR ALTER TRIGGER VALS_LINKS_BI FOR VALS_LINKS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_vals_links_id,1);
new.insertdt=current_timestamp;
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
execute ibeblock
as
begin
cbb = 'execute ibeblock (RecCount variant)
as
begin
if (ibec_Mod(RecCount, 100) = 0) then
ibec_Progress(RecCount || '' records imported.'');
end;';
DB = ibec_CreateConnection(__ctFirebird, 'DBName="standart-n:D:\STANDART-N\base_g\test\test_ztrade_g.fdb";
ClientLib=gds32.dll;
User=SYSDBA; Password=masterkey; Names=WIN1251; SqlDialect=3;');
try
ibec_UseConnection(DB);
Res = ibec_ImportData(DB, 'VALS_LINKS', __impDBase, 'D:\STANDART-N\base_g\test\SQLs\vals_links.dbf', '',
'RowFirst=1; RowLast=2147483647;
DateOrder=DMY; DateSeparator="."; TimeSeparator=":";', '', cbb);
if (Res is not null) then
ibec_ShowMessage(Res || ' records were imported successfully.');
finally
ibec_CloseConnection(DB);
end;
end
Выпоняем cowa_all.sql (не оптимизирован для ЙО)
--копируем VALS_LINKS как объект
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;
ALTER TRIGGER PARTS_AU0 INACTIVE;
ALTER TRIGGER VALS_AD_DISTR INACTIVE;
--ALTER TRIGGER VALS_AD_DODOUBLES INACTIVE;
--ALTER TRIGGER WARES_BIU_VALCHECK INACTIVE;
--ALTER TRIGGER WARES_AD_DISTR INACTIVE;
--ALTER TRIGGER WARES_AD0 INACTIVE;
ALTER TRIGGER WARES_BD0 INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
--ALTER TRIGGER WARES_BU0_MGN_NAME_ASNA INACTIVE;
--DROP INDEX WARES_IDX15;
--CREATE INDEX WARES_IDX15 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID);
ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;
CREATE INDEX WARES_LOG_ACTUAL_WARE_ID ON WARES_LOG (ACTUAL_WARE_ID);
SET TERM ^ ;
CREATE OR ALTER trigger wares_log_bi_tmp for wares_log
active before insert position 0
AS
begin
if (new.log_id is null) then
new.log_id=UUID_TO_CHAR(GEN_UUID());
new.d$uuid=new.log_id;
if (new.d$srvupddt is null) then
begin
new.d$srvupddt='2000-01-01';
end
if (new.l_id is null) then
new.l_id=GEN_ID(GEN_WARES_LOG_ID,1);
new.log_insertdt=current_timestamp;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
begin
delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id));
for
select min(d$uuid), name_id, izg_id, country_id, barcode from wares group by name_id, izg_id, country_id, barcode having count(1)>1
into actual_uuid, name_id, izg_id, country_id, barcode
do
begin
for select d$uuid from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode and d$uuid<>:actual_uuid into uuid
do begin
-- insert into tmp_todelete (TABLENAME,UUID,REASON,ACTUAL_UUID) values ('WARES',:uuid,'удаление',:actual_uuid);
insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,:actual_uuid
from wares where d$uuid=:uuid;
update parts set ware_id=:actual_uuid where ware_id=:uuid;
delete from wares where d$uuid=:uuid;
end
end
--delete from wares w where not exists (select id from parts p where p.ware_id=w.id) = всего 11414 , после сжатия - игнорим пока
delete from vals v where (vtype=0) and (not exists (select id from wares w where w.name_id=v.id)) and (not exists (select id from wares_log wl where wl.name_id=v.id));
delete from vals v where (vtype=3) and (not exists (select id from wares w where w.izg_id=v.id)) and (not exists (select id from wares_log wl where wl.izg_id=v.id));
delete from vals v where (vtype=2) and (not exists (select id from wares w where w.country_id=v.id)) and (not exists (select id from wares_log wl where wl.country_id=v.id));
delete from vals v where (vtype=1) and (not exists (select id from wares w where w.orig_name_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_name_id=v.id));
delete from vals v where (vtype=6) and (not exists (select id from wares w where w.orig_izg_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_izg_id=v.id));
delete from vals v where (vtype=5) and (not exists (select id from wares w where w.orig_country_id=v.id)) and (not exists (select id from wares_log wl where wl.orig_country_id=v.id));
end^
SET TERM ; ^
update wares set barcode='' where barcode is null;
execute procedure PR_TMP_CHANGEWARES_CONSTR;
commit work;
CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID, IZG_ID, COUNTRY_ID, BARCODE);
--DROP TRIGGER WARES_LOG_BI_TMP;
CREATE TABLE TMP_UPDATED_WARES (
UUID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */
);
CREATE INDEX TMP_UPDATED_WARES_IDX1 ON TMP_UPDATED_WARES (UUID);
/*
CREATE GENERATOR GEN_VALS_CARANTINE_ID;
CREATE TABLE VALS_CARANTINE (
ID DM_ID NOT NULL,
CUR_ID DM_UUID,
NEW_ID DM_UUID,
INSERTDT DM_DATETIME,
FROM_PROFILE_ID DM_ID_NULL
);
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_CARANTINE_BI FOR VALS_CARANTINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_vals_carantine_id,1);
new.insertdt=current_timestamp;
if (new.from_profile_id is null) then
select from_profile_id from g$distribute where uuid=new.new_id into new.from_profile_id;
end
^
SET TERM ; ^
*/
SET TERM ^ ;
CREATE OR ALTER trigger vals_biu_dodoubles for vals
inactive before insert or update position 0
AS
declare variable ID type of DM_UUID;
begin
select id from vals where VTYPE=new.VTYPE and svalue=new.svalue and ALTTYPE=new.ALTTYPE and d$uuid<>new.d$uuid into :id;
if (id is null) then exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
if (new.vtype=0) then
update wares set name_id=new.id where name_id=:id;
else if (new.vtype=3) then
update wares set izg_id=new.id where izg_id=:id;
else if (new.vtype=2) then
update wares set country_id=new.id where country_id=:id;
else if (new.vtype=1) then
update wares set orig_name_id=new.id where orig_name_id=:id;
else if (new.vtype=6) then
update wares set orig_izg_id=new.id where orig_izg_id=:id;
else if (new.vtype=5) then
update wares set orig_country_id=new.id where orig_country_id=:id;
else exit;
insert into vals_carantine (cur_id, new_id) values (:id, new.id);
delete from vals where id=:id;
end^
SET TERM ; ^
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
cur_id=null;
select 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 d$uuid<>new.d$uuid into :cur_id;
if (cur_id is null) then exit;
if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then
begin
update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id;
insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,new.d$uuid from wares where d$uuid=:cur_id;
-- values (new.ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,new.BARCODE,new.Z_ID,new.SKLAD_ID,new.INSERTDT,new.PACKET,new.L_ID,new.d$uuid);
end
update parts set ware_id=new.d$uuid where ware_id=:cur_id;
delete from wares where d$uuid=:cur_id;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger parts_biu_cowa for parts
active before insert or update position 0
AS
declare variable ware_id type of DM_UUID;
begin
if (exists(select id from wares where id=new.ware_id)) then exit;
select max(actual_ware_id) from wares_log where id=new.ware_id and (exists(select id from wares w where w.d$uuid=actual_ware_id)) into :ware_id;
if (ware_id is null) then exit;
-- if (exists(select id from wares where d$uuid=:ware_id)) then
new.ware_id=:ware_id;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure UTPR_REMOVE_TILDA (
SVALUE type of DM_TEXT)
returns (
S type of DM_TEXT)
as
declare variable TILDA type of DM_TEXT;
declare variable C char(10);
begin
s=:svalue;
if (svalue not starting with '~') then
begin
suspend;
exit;
end
tilda='';
if (svalue starting with '~100') then
begin
s=substring(svalue from 5 for 245);
tilda='100';
end
else
begin
c=substring(svalue from 3 for 1);
if (c in ('0','1','2','3','4','5','6','7','8','9')) then
begin
s=substring(svalue from 4 for 245);
tilda=substring(svalue from 2 for 2);
end
else
begin
s=substring(svalue from 3 for 245);
tilda=substring(svalue from 2 for 1);
end
c=substring(s from 1 for 1);
if (c='@') then
begin
s=substring(s from 2);
end
end
suspend;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure UTPR_BUBBLE_SORT (
SOURCE DM_TEXT)
returns (
S DM_TEXT)
as
declare variable C1 char(1);
declare variable L integer;
declare variable I integer;
declare variable SWAPPED integer;
declare variable J integer;
declare variable C2 char(1);
declare variable B char(1);
begin
l=char_length(source);
i=0;
s=source;
while (i<=l) do
begin
i=i+1; j=0;
swapped=0;
while (j<=l-i-1) do
begin
j=j+1;
c1=substring(s from j for 1);
c2=substring(s from j+1 for 1);
if (c1>c2) then
begin
b=c1;
s=substring(s from 1 for j - 1) || c2 || c1 || substring(s from j + 2);
swapped=1;
end
end
if (swapped=0) then break;
end
suspend;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure UTPR_CHARSONLY (
SOURCE type of DM_TEXT,
SORTED integer = 0)
returns (
S DM_TEXT)
as
declare variable C char(1);
declare variable L integer;
declare variable I integer;
declare variable V integer;
begin
l=char_length(source);
i=1;
s='';
while (i<=l) do
begin
c=substring(source from i for 1);
v=ascii_val(c);
if (((v>47) and (v<58)) or
((v>64) and (v<91)) or
((v>96) and (v<123)) or
(v>191)) then
s=s||c;
i=i+1;
end
if (sorted=1) then
select s from UTPR_BUBBLE_SORT(:s) into :s;
suspend;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure PR_TMP_COMPRESS_VALS
as
declare variable UVALUE type of DM_TEXT;
declare variable VTYPE type of DM_STATUS;
declare variable ID type of DM_UUID;
declare variable W_CNT type of DM_STATUS;
declare variable T_CNT type of DM_STATUS;
declare variable T_ID type of DM_UUID;
begin
-- update vals set uppervalue=upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))), isactual=0;
update vals set uppervalue=(select s from UTPR_CHARSONLY(upper(trim((select s from UTPR_REMOVE_TILDA(svalue)))),1)), isactual=0;
-- for select uppervalue, vtype from vals where vtype in (0,3,2) and svalue<>'' group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
for select uppervalue, vtype from vals where vtype in (0,3,2) group by vtype, uppervalue having count(1)>1 into uvalue, vtype do
begin
T_CNT=0; t_id=null;
for select id from vals where vtype=:vtype and uppervalue=:uvalue into id do
begin
if (vtype=0) then
select count(1) from wares where name_id=:id into :w_cnt;
else if (vtype=3) then
select count(1) from wares where izg_id=:id into :w_cnt;
else if (vtype=2) then
select count(1) from wares where country_id=:id into :w_cnt;
if (t_cnt<w_cnt) then
begin
t_cnt=w_cnt;
t_id=:id;
end
update vals set isactual=:w_cnt where id=:id;
end
if (t_id is not null) then
begin
for select id from vals where vtype=:vtype and uppervalue=:uvalue and id<>:t_id into id do
begin
if (vtype=0) then
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:id;
update wares set name_id=:t_id where name_id=:id;
end
else if (vtype=3) then
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:id;
update wares set izg_id=:t_id where izg_id=:id;
end
else if (vtype=2) then
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:id;
update wares set country_id=:t_id where country_id=:id;
end
end
end
end
end^
SET TERM ; ^
CREATE INDEX VALS_IDX_COWA_TMP ON VALS (VTYPE, UPPERVALUE);
execute procedure PR_TMP_COMPRESS_VALS;
commit work;
SET TERM ^ ;
CREATE OR ALTER procedure PR_TMP_APPLY_VALS_LINKS
as
declare variable RIGHT_ID type of DM_UUID;
declare variable WRONG_ID type of DM_UUID;
begin
for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=0 into right_id, wrong_id do
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where name_id=:wrong_id;
update wares set name_id=:right_id where name_id=:wrong_id;
end
for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=3 into right_id, wrong_id do
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where izg_id=:wrong_id;
update wares set izg_id=:right_id where izg_id=:wrong_id;
end
for select vl.right_val_id, vl.wrong_val_id from vals_links vl where vtype=2 into right_id, wrong_id do
begin
insert into TMP_UPDATED_WARES (uuid) select d$uuid from wares where country_id=:wrong_id;
update wares set country_id=:right_id where country_id=:wrong_id;
end
end^
SET TERM ; ^
execute procedure PR_TMP_APPLY_VALS_LINKS;
commit work;
DROP TRIGGER WARES_LOG_BI_TMP;
ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_AIU0 INACTIVE;
ALTER TRIGGER VALS_AIU0 INACTIVE;
ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_LOG_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;
ALTER TRIGGER PARTS_AU0 ACTIVE;
Для связывания по ШК выполняем cowa_bcode_links.sql (не оптимизирован для ЙО)
/******************************************************************************/ /*** Generated by IBExpert 19.08.2016 13:46:31 ***/ /******************************************************************************/
/******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3;
/******************************************************************************/ /*** Tables ***/ /******************************************************************************/
CREATE GENERATOR GEN_BARCODE_WARE_ID;
CREATE TABLE BARCODE_WARE (
WARE_ID DM_UUID NOT NULL, BARCODE DM_TEXT, ID DM_ID NOT NULL, NAME_ID DM_UUID, IZG_ID DM_UUID, COUNTRY_ID DM_UUID, SNAME DM_TEXT, SIZG DM_TEXT, SCOUNTRY DM_TEXT
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE BARCODE_WARE ADD CONSTRAINT PK_BARCODE_WARE PRIMARY KEY (ID);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX BARCODE_WARE_IDX1 ON BARCODE_WARE (NAME_ID); CREATE INDEX BARCODE_WARE_IDX2 ON BARCODE_WARE (IZG_ID); CREATE INDEX BARCODE_WARE_IDX3 ON BARCODE_WARE (COUNTRY_ID);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/
/* Trigger: BARCODE_WARE_BI */ CREATE OR ALTER TRIGGER BARCODE_WARE_BI FOR BARCODE_WARE ACTIVE BEFORE INSERT POSITION 0 as begin
if (new.id is null) then new.id = gen_id(gen_barcode_ware_id,1); select svalue from vals where id=new.name_id into new.sname; select svalue from vals where id=new.izg_id into new.sizg; select svalue from vals where id=new.country_id into new.scountry;
end ^
SET TERM ; ^
/******************************************************************************/ /*** Privileges ***/ /******************************************************************************/
SET TERM ^ ;
CREATE OR ALTER procedure UTPR_EXPLODE (
SOURCE type of DM_BLOBTEXT, SEPARATOR type of DM_TEXT)
returns (
S type of DM_TEXT)
as declare variable I integer; declare variable L integer; declare variable O integer; begin
o=1; l=char_length(separator); i=position(SEPARATOR,SOURCE); while (i>0) do begin s=substring(source from o for i-o); suspend; o=i+1; i=position(SEPARATOR,SOURCE,o); end s=substring(source from o for char_length(source)); suspend;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure PRCOWA_BARCODE_LINKS as declare variable NAME_ID type of DM_UUID; declare variable IZG_ID type of DM_UUID; declare variable COUNTRY_ID type of DM_UUID; declare variable BARCODE type of DM_TEXT; declare variable ID DM_UUID; declare variable SCOUNTRY DM_TEXT; declare variable SIZG DM_TEXT; begin --заполнение
delete from barcode_ware;
for select name_id, izg_id, country_id, barcode, id from wares where barcode<> and barcode is not null into name_id, izg_id, country_id, barcode, :id do
begin
for select trim(s) from UTPR_EXPLODE(:barcode,',') into :barcode do
begin
if (barcode<>) then
insert into barcode_ware (ware_id, barcode, name_id, izg_id, country_id) values (:id,:barcode,:name_id, :izg_id, :country_id);
end
end
-- обработка
for select barcode, name_id, --count(1) as total--, count(distinct izg_id) as izg, count(distinct country_id) as country max(sizg) as sizg, max(scountry) as scountry
-- from barcode_ware group by barcode, name_id having ((count(1)>count(distinct izg_id)) and count(distinct izg_id)>1) or ((count(1)>count(distinct country_id)) and count(distinct country_id)>1)
from barcode_ware group by barcode, name_id having (count(distinct izg_id)>1) or (count(distinct country_id)>1)
into barcode, name_id, sizg, scountry do
begin
if (trim(sizg)<>) then
begin
izg_id=null;
select id from vals where svalue=:sizg and vtype=3 into :izg_id;
for select ware_id from barcode_ware where barcode=:barcode and name_id=:name_id and sizg= into :id do
begin
update wares set izg_id=:izg_id where id=:id;
insert into TMP_UPDATED_WARES(uuid) values (:id);
end
end
if (trim(scountry)<>) then
begin
country_id=null;
select id from vals where svalue=:scountry and vtype=2 into :country_id;
for select ware_id from barcode_ware where barcode=:barcode and name_id=:name_id and scountry= into :id do
begin
update wares set country_id=:country_id where id = :id;
insert into TMP_UPDATED_WARES(uuid) values (:id);
end
end
end
end^
SET TERM ; ^
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE; ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE; ALTER TRIGGER WARES_BI_DODOUBLES INACTIVE; ALTER TRIGGER WARES_AD_DISTR INACTIVE; ALTER TRIGGER PARTS_BIU0_SAGENT INACTIVE; ALTER TRIGGER PARTS_BU_SRVSYNC INACTIVE; ALTER TRIGGER PARTS_AU0 INACTIVE;
SET TERM ^ ;
CREATE OR ALTER trigger wares_log_bi_tmp for wares_log active before insert position 0 AS begin
if (new.log_id is null) then new.log_id=UUID_TO_CHAR(GEN_UUID()); new.d$uuid=new.log_id; if (new.d$srvupddt is null) then begin new.d$srvupddt='2000-01-01'; end if (new.l_id is null) then new.l_id=GEN_ID(GEN_WARES_LOG_ID,1); new.log_insertdt=current_timestamp;
end^
SET TERM ; ^
execute procedure PRCOWA_BARCODE_LINKS;
commit work;
DROP TRIGGER WARES_LOG_BI_TMP; ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE; ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE; ALTER TRIGGER WARES_BI_DODOUBLES ACTIVE; ALTER TRIGGER WARES_AD_DISTR ACTIVE; ALTER TRIGGER PARTS_BIU0_SAGENT ACTIVE; ALTER TRIGGER PARTS_BU_SRVSYNC ACTIVE; ALTER TRIGGER PARTS_AU0 ACTIVE;
Проверяем работу, смотрим сколько записей добавилось в g$distribute - могут добавится немного записей и только по таблицам wares и wares_log
Экспорт как insert скрипт в файл запроса "select * from wares_log wl where wl.actual_ware_id is not null", все строки "commit work" - убираем
Экспрорт как update or insert скрипт в файл запроса "select distinct w.* from TMP_UPDATED_WARES t left join wares w on t.uuid=w.d$uuid where w.d$uuid is not null", все строки "commit work" - убираем
Делаем шаблоны G$TASKS_TMPL по образцу. Заменить data в записях cowa_02XX на файл, созданный в 6м пункте, cowa_08 - на файл 7го пункта
/******************************************************************************/
/*** Generated by IBExpert 25.08.2016 14:49:37 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE G$TASKS_TMPL (
ID DM_ID NOT NULL,
PROFILE_ID DM_ID,
TASK_TYPE DM_STATUS,
CAPTION DM_TEXT,
DATA DM_BLOBTEXT,
INSERTDT DM_DATETIME,
SENDDT DM_DATETIME,
ENDDT DM_DATETIME,
ENDFLAG DM_STATUS,
ENDTEXT DM_BLOBTEXT,
ENDDATA DM_BLOBTEXT,
G$PROFILE_ID DM_ID_NULL,
D$UUID DM_UUID_NULL,
D$SRVUPDDT DM_DATETIME,
CHECKPREVTASK DM_STATUS
);
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348162, 0, 0, 'cowa_01', 'SET TERM ^ ;
CREATE trigger wares_log_bi_cowatmp for wares_log
active before insert position 0
AS
begin
if (new.sklad_id is null) then
new.sklad_id='''';
end^
SET TERM ; ^
CREATE TABLE TMP_LOG (
SKEY DM_TEXT,
SVALUE DM_TEXT);
ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;
CREATE INDEX WARES_LOG_ACTUAL_WARE_ID
ON WARES_LOG (ACTUAL_WARE_ID);
CREATE GENERATOR GEN_VALS_CARANTINE_ID;
CREATE TABLE VALS_CARANTINE (
ID DM_ID NOT NULL,
CUR_ID DM_UUID,
NEW_ID DM_UUID,
INSERTDT DM_DATETIME,
FROM_PROFILE_ID DM_ID_NULL
);
SET TERM ^ ;
CREATE OR ALTER TRIGGER VALS_CARANTINE_BI FOR VALS_CARANTINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_vals_carantine_id,1);
new.insertdt=current_timestamp;
if (new.from_profile_id is null) then
select from_profile_id from g$distribute where uuid=new.new_id into new.from_profile_id;
end
^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger vals_biu_dodoubles for vals
active before insert or update position 0
AS
declare variable ID type of DM_UUID;
begin
select id from vals where VTYPE=new.VTYPE and svalue=new.svalue and ALTTYPE=new.ALTTYPE and d$uuid<>new.d$uuid into :id;
if (id is null) then exit;
/*
0 - приведенное наименование
3 - приведенный производитель
2 - приведенная страна
1 - ориг. наименование
6 - ориг. производитель
5 - ориг. страна
Запускает цепочку апдейтов. При изменении wares проверяются дубли, и, в случае нахождения, правятся партии
*/
if (new.vtype=0) then
update wares set name_id=new.id where name_id=:id;
else if (new.vtype=3) then
update wares set izg_id=new.id where izg_id=:id;
else if (new.vtype=2) then
update wares set country_id=new.id where country_id=:id;
else if (new.vtype=1) then
update wares set orig_name_id=new.id where orig_name_id=:id;
else if (new.vtype=6) then
update wares set orig_izg_id=new.id where orig_izg_id=:id;
else if (new.vtype=5) then
update wares set orig_country_id=new.id where orig_country_id=:id;
else exit;
insert into vals_carantine (cur_id, new_id) values (:id, new.id);
delete from vals where id=:id;
end^
SET TERM ; ^
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
select 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 d$uuid<>new.d$uuid into :cur_id;
if (cur_id is null) then exit;
if (not exists(select id from wares_log where id=:cur_id and actual_ware_id=new.id)) then
begin
update wares_log set ACTUAL_WARE_ID=new.d$uuid where ACTUAL_WARE_ID=:cur_id;
insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID)
select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,new.d$uuid from wares where d$uuid=:cur_id;
end
update parts set ware_id=new.d$uuid where ware_id=:cur_id;
update doc_detail_active set ware_id=new.d$uuid where ware_id=:cur_id;
--20160819 А.К.
update warebase_distr set ware_id=new.d$uuid where ware_id=:cur_id;
update doc_detail_active_treb set ware_id=new.d$uuid where ware_id=:cur_id;
update DOC_DETAIL_DELETED set ware_id=new.d$uuid where ware_id=:cur_id;
update DOC_DETAIL_VIRTUAL set ware_id=new.d$uuid where ware_id=:cur_id;
update WARES_TREB set ware_id=new.d$uuid where ware_id=:cur_id;
delete from wares where d$uuid=:cur_id;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger parts_biu_cowa for parts
active before insert or update position 0
AS
declare variable ware_id type of DM_UUID;
begin
if (exists(select id from wares where id=new.ware_id)) then exit;
-- select max(actual_ware_id) from wares_log where id=new.ware_id into :ware_id;
select max(actual_ware_id) from wares_log where id=new.ware_id and (exists(select id from wares w where w.d$uuid=actual_ware_id)) into :ware_id;
if (ware_id is null) then exit;
-- if (exists(select id from wares where d$uuid=:ware_id)) then
new.ware_id=:ware_id;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger wares_bd_cowa for wares
active before delete position 0
AS
declare variable ware_id type of DM_UUID;
begin
if (not exists (select id from parts where ware_id=old.id)) then exit;
-- select max(actual_ware_id) from wares_log where id=old.id into ware_id;
select max(actual_ware_id) from wares_log wl where id=old.id and (exists (select id from wares w where w.id=wl.actual_ware_id)) into ware_id;
if (ware_id is null) then
insert into tmp_log (skey, svalue) values (''Нет ware по acual_ware!'',old.id);
else
begin
update parts set ware_id=:ware_id where ware_id=old.id;
--20160819 А.К.
update doc_detail_active set ware_id=:ware_id where ware_id=old.id;
update warebase_distr set ware_id=:ware_id where ware_id=old.id;
update doc_detail_active_treb set ware_id=:ware_id where ware_id=old.id;
update DOC_DETAIL_DELETED set ware_id=:ware_id where ware_id=old.id;
update DOC_DETAIL_VIRTUAL set ware_id=:ware_id where ware_id=old.id;
update WARES_TREB set ware_id=:ware_id where ware_id=old.id;
end
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER procedure PR_TMP_CHANGEWARES_CONSTR
as
declare variable UUID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable ACTUAL_UUID type of DM_UUID;
declare variable EXISTING_ACTUAL_WARE_ID type of DM_UUID;
begin
-- delete from wares_log wl where (ACTUAL_WARE_ID is null) and (not exists (select id from wares w where w.id=wl.id));
for
select min(d$uuid), name_id, izg_id, country_id, barcode from wares group by name_id, izg_id, country_id, barcode having count(1)>1
into actual_uuid, name_id, izg_id, country_id, barcode
do
begin
EXISTING_ACTUAL_WARE_ID=null;
select 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 EXISTING_ACTUAL_WARE_ID;
if (EXISTING_ACTUAL_WARE_ID is not null) then
begin
if (exists (select id from wares where d$uuid=:existing_actual_ware_id)) then
actual_uuid=EXISTING_ACTUAL_WARE_ID;
end
for select d$uuid from wares where name_id=:name_id and izg_id=:izg_id and country_id=:country_id and barcode=:barcode and d$uuid<>:actual_uuid into uuid
do begin
insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID,d$srvupddt)
select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,:actual_uuid, current_timestamp
from wares where d$uuid=:uuid;
--на триггере update parts set ware_id=:actual_uuid where ware_id=:uuid;
delete from wares where d$uuid=:uuid;
end
end
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger wares_log_ai_cowatmp for wares_log
active after insert position 0
AS
begin
if (new.actual_ware_id is null) then exit;
if (exists(select id from wares where id=new.actual_ware_id)) then
delete from wares where id=new.id;
else
insert into tmp_log (skey,svalue) values (''WARES_LOG_AI_COWATMP'',new.actual_ware_id);
end^
SET TERM ; ^
ALTER TRIGGER WARES_AD_DISTR INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_BI_DISTR INACTIVE;
ALTER TRIGGER WARES_LOG_AI0 INACTIVE;
ALTER TRIGGER PARTS_AU0 INACTIVE;
ALTER TRIGGER PARTS_BU_DISTR INACTIVE;
ALTER TRIGGER PARTS_BU_G$SYNC INACTIVE;
ALTER TRIGGER PARTS_BU0 INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348163, 0, 0, 'cowa_02', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348164, 0, 0, 'cowa_03', 'ALTER TRIGGER PARTS_BU_DISTR ACTIVE;
ALTER TRIGGER PARTS_BU_G$SYNC ACTIVE;
ALTER TRIGGER PARTS_BU0 ACTIVE;
ALTER TRIGGER WARES_LOG_BI_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_BU_DISTR ACTIVE;
CREATE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE);
ALTER TABLE WARES DROP CONSTRAINT UNQ1_WARES;
ALTER TABLE WARES DROP CONSTRAINT WARES_IDX1;
CREATE INDEX WARES_IDX1 ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID);
DROP TRIGGER wares_log_bi_cowatmp;
ALTER TRIGGER WARES_AU0 INACTIVE;
ALTER TRIGGER WARES_BU0 INACTIVE;
ALTER TRIGGER WARES_BIU_COWA INACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 INACTIVE;
ALTER TRIGGER WARES_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348165, 0, 0, 'cowa_04', 'update wares set barcode='''' where barcode is null;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348166, 0, 0, 'cowa_05', 'ALTER TRIGGER WARES_BU0 ACTIVE;
ALTER TRIGGER WARES_BIU_COWA ACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 ACTIVE;
ALTER TRIGGER WARES_BU_DISTR ACTIVE;
CREATE UNIQUE INDEX WARES_IDX_UUID
ON WARES (D$UUID);
ALTER TRIGGER WARES_AD_DISTR INACTIVE;
ALTER TRIGGER PARTS_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348167, 0, 0, 'cowa_06', 'execute procedure PR_TMP_CHANGEWARES_CONSTR;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348168, 0, 0, 'cowa_07', 'DROP INDEX WARES_IDX_U;
CREATE UNIQUE INDEX WARES_IDX_U ON WARES (NAME_ID,IZG_ID,COUNTRY_ID,BARCODE);
DROP TRIGGER WARES_LOG_AI_COWATMP;
DROP INDEX WARES_IDX100;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348169, 0, 0, 'cowa_08', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348170, 0, 0, 'cowa_09', 'execute procedure DBGPR_MAKEWAREBASEFROMDOCS(0);', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348171, 0, 0, 'cowa_10', 'ALTER TRIGGER PARTS_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_AD_DISTR ACTIVE;
ALTER TRIGGER WARES_AU0 ACTIVE;
ALTER TRIGGER WARES_LOG_AI0 ACTIVE;
ALTER TRIGGER PARTS_AU0 ACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348172, 0, 0, 'cowa_1_whash', 'SET TERM ^ ;
CREATE OR ALTER procedure PR_GET_WARES_HASH (
NAME_ID DM_UUID_NULL,
IZG_ID DM_UUID_NULL,
COUNTRY_ID DM_UUID_NULL,
BARCODE 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, ''''));
else
whash = hash(coalesce(:NAME_ID,''NAME_ID'')||coalesce(:IZG_ID,''IZG_ID'')||coalesce(:COUNTRY_ID,''COUNTRY_ID''));
suspend;
end^
SET TERM ; ^
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
declare variable WHASH DM_ID;
begin
if (orig_code is null) then orig_code='''';
if (searchinlogs is null) then searchinlogs=0;
if (searchinlogs=1) then
begin
select first 1 id from wares_log 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
and barcode=:barcode
into :ware_id;
if ((select id from wares where id = :ware_id) is null) then ware_id = null;
end
else
begin
--20160805 A.K.
select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE) into :whash;
select id from wares where whash = :whash into :ware_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
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 ; ^
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,
SKLAD_ID DM_TEXT,
ALTTYPE DM_STATUS,
MNN DM_TEXT = '''')
returns (
W_ID type of DM_UUID_NULL)
as
declare variable OLD_BARCODE DM_TEXT1024;
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;
declare variable WHASH DM_ID;
begin
if (orig_code is null) then orig_code='''';
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;
--20160805 A.K.
select whash from PR_GET_WARES_HASH(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE) into :whash;
select id from wares where whash = :whash into :w_id;
/*select 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 and barcode=:barcode 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
--else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
suspend;
end^
SET TERM ; ^
SET TERM ^ ;
CREATE OR ALTER trigger wares_hash_bui0 for wares
active before insert or update position 0
AS
begin
select whash from PR_GET_WARES_HASH(new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.BARCODE) into new.whash;
end^
SET TERM ; ^
ALTER TRIGGER WARES_BU_DISTR INACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 INACTIVE;
ALTER TRIGGER WARES_BU0 INACTIVE;
ALTER TRIGGER WARES_AU0 INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348173, 0, 0, 'cowa_2_whash', 'update wares set id=id;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348174, 0, 0, 'cowa_3_whash', 'ALTER TRIGGER WARES_BU_DISTR ACTIVE;
ALTER TRIGGER WARES_SNAME_BU0 ACTIVE;
ALTER TRIGGER WARES_BU0 ACTIVE;
ALTER TRIGGER WARES_AU0 ACTIVE;
CREATE UNIQUE INDEX WARES_IDX100 ON WARES (WHASH);', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348175, 0, 0, 'cowa_1_recur', 'SET TERM ^ ;
CREATE OR ALTER procedure PR_COWA_01
as
declare variable I integer;
declare variable ACTUAL_WARE_ID type of DM_UUID;
declare variable ACTUAL_WARE_ID1 type of DM_UUID;
begin
i=1;
while (i>0) do
begin
i=0;
for select distinct actual_ware_id from wares_log wl where actual_ware_id is not null and not exists (select id from wares w where w.id=wl.actual_ware_id)
into :actual_ware_id do
begin
for select distinct actual_ware_id from wares_log wl where id = :actual_ware_id and actual_ware_id is not null
into ACTUAL_WARE_ID1 do
begin
i=i+1;
update wares_log set actual_ware_id=:ACTUAL_WARE_ID1 where actual_ware_id=:ACTUAL_WARE_ID;
end
end
end
end^
SET TERM ; ^
ALTER TRIGGER WARES_LOG_BU_DISTR INACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348200, 0, 0, 'cowa_bizon', '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_UUID_NULL;
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_UUID_NULL;
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_UUID_NULL;
begin
select bcode_izg, ware_id,
(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_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,
(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_name_id, ddalog_izg_id, ddalog_country_id, ddalog_orig_nameid, ddalog_orig_izgid, ddalog_orig_countryid, ddalog_z_id, ddalog_sklad_id;
select first 1 skip 1 bcode_izg, ware_id,
(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 ddalog2_barcode, ddalog2_ware_id, ddalog2_name_id, ddalog2_izg_id, ddalog2_country_id, ddalog2_orig_nameid, ddalog2_orig_izgid, ddalog2_orig_countryid, ddalog2_z_id, ddalog2_sklad_id;
if ( ((dda_ware_id <> ''-1'') and (dda_ware_id <> ''0'') and (dda_ware_id is not null)) and
((ddalog2_ware_id <> ''-1'') and (ddalog2_ware_id <> ''0'') and (ddalog2_ware_id is not null))
and (dda_barcode = ddalog2_barcode)
and (dda_ware_id <> ddalog2_ware_id) )
then
begin
update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog2_ware_id;
exit;
end
if ( (ddalog_ware_id = ''0'') or (ddalog_ware_id = ''-1'') or (ddalog_ware_id is null) ) then
begin
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
else if ( ((dda_ware_id <> ''0'') and (dda_ware_id <> ''-1'') and (dda_ware_id is not null)) and
((dda_name_id <> ddalog_name_id) or (dda_izg_id <> ddalog_izg_id) or (dda_country_id <> ddalog_country_id) or (dda_orig_nameid <> ddalog_orig_nameid)
or (dda_orig_izgid <> ddalog_orig_izgid) or (dda_orig_countryid <> ddalog_orig_countryid))
and (dda_barcode = ddalog_barcode)
) then
begin
update wares set name_id = :dda_name_id, izg_id = :dda_izg_id, country_id = :dda_country_id, orig_name_id = :dda_orig_nameid,
orig_izg_id = :dda_orig_izgid, orig_country_id = :dda_orig_countryid, barcode = :dda_barcode where id = :ddalog_ware_id;
end
end;^
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 val_id from pr_getval_id(:sname,0,:alttype) into :name_id;
select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
*/
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^
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
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;
*/
select ID from WARES where NAME_ID = :NAME_ID and IZG_ID = :IZG_ID and COUNTRY_ID = :COUNTRY_ID and BARCODE = :BARCODE 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;^
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 first 1 iif(actual_ware_id is null, id, actual_ware_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;
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;^
create or alter procedure PR_DOC_COMMIT (
DOC_ID type of DM_ID,
SESSION_ID type of DM_ID)
as
declare variable WRONG_WARES DM_STATUS;
declare variable BASE_TYPE type of DM_STATUS;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable W_PARAM DM_STATUS;
declare variable WARES_MODE DM_STATUS;
begin
select DT.BASE_TYPE, D.PRICE_TYPE, D.CONTRACT_ID from DOCS D
left join DOC_TYPES DT on D.DOC_TYPE = DT.ID where D.ID = :DOC_ID into :BASE_TYPE, :PRICE_TYPE, :CONTRACT_ID;
if (BASE_TYPE in (1, 3, 6, 8)) then
begin
wrong_wares = 0; wares_mode = 1;
select PARAM_VALUE from PR_GETPARAMVALUE(''WARES_MODE'', 0) into wares_mode;
if (wares_mode = 0) then
begin
select count(id) from doc_detail_active where doc_id = :doc_id and ((ware_id = ''0'') or (ware_id = ''-1'') or (ware_id = '''')) into wrong_wares;
if (wrong_wares > 0) then exception EX_WRONG_OPER;
end
execute procedure PR_DOC_PRIHOD_COMMIT(:DOC_ID, 0);
end
else
if (BASE_TYPE in (2, 7, 9)) then
execute procedure PR_DOC_RASHOD_COMMIT(:DOC_ID, 0);
else
exception EX_WRONGDOC_BASE_TYPE;
delete from DOC_DETAIL_ACTIVE where DOC_ID = :DOC_ID;
if (BASE_TYPE = 3) then
begin
delete from WAREBASE where PART_ID in (select PART_ID from DOC_DETAIL where DOC_ID = :DOC_ID and QUANT < 0 and PART_TYPE = 1);
end
select sum(SUMMA), sum(SUMMA_O), sum(SUM_NDSO), sum(PRICE * QUANT / 10000), sum(SUM_DSC) from DOC_DETAIL where DOC_ID = :DOC_ID into :SUMMA,
:SUMMA_O, :SUM_NDSO, :DSCSUMMA, :SUM_DSC;
DSCSUMMA = DSCSUMMA * 10000;
update DOCS set COMMITSESSION_ID = :SESSION_ID, STATUS = 1, POSTDT = ''now'',
CHECKDATA = (select CHECKDATA from PR_DOCCHECKDATA(:DOC_ID)), SUMMA = :SUMMA, SUMMA_O = :SUMMA_O,
SUM_NDSO = :SUM_NDSO, CALCSUMMA = :DSCSUMMA, SUM_DSC = :SUM_DSC, CASHDATA = (iif(:BASE_TYPE = 1, -:SUMMA, null)) where ID = :DOC_ID;
execute procedure PR_UPDBLOCKINFO_BY_DOC(:DOC_ID, 1);
--if ((base_type = 2) and (contract_id<>0)) then
if (CONTRACT_ID <> 0) then
begin
if (BASE_TYPE = 2) then
execute procedure PR_CREDITCOMMIT(:CONTRACT_ID);
if (BASE_TYPE in (8, 9)) then
execute procedure PR_CREDITREINITCOMMIT(:CONTRACT_ID, :SESSION_ID);
-- execute procedure PR_CREDITCOMMIT(:contract_id);
end
execute procedure PR_DOEVENT(''EV_DOCCOMMIT'');
end;^
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 0
as
begin
--Если в документ ввод услуг вставлять через буфер обмена, то тип строк - товар, а не услуга
if ((select dt.base_type from docs d left join doc_types dt on dt.id=d.doc_type where d.id=new.doc_id)=6) then
new.part_type=1;
if (new.ID is null) then
new.ID = gen_id(GEN_DOC_DETAIL_ACTIVE_ID, 1);
new.INSERTDT = ''now'';
if (new.BASE_AGENT_ID is null) then
new.BASE_AGENT_ID = 0;
if (new.STATUS is null) then
new.STATUS = 0;
if (new.PARENT_ID is null) then
new.PARENT_ID = 0;
if (new.PART_TYPE is null) then
new.PART_TYPE = 0;
--первая строчка = до привидения, оригианльные значения накладной
select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID;
insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
BARCODE1, BCODE_IZG,
GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
PART_TYPE, BASE_AGENT_ID)
values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN,
new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID,
new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY,
new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID,
new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
--
if ((new.PART_ID is null) or (new.PART_ID = 0)) then
begin
-->cf20110923
--if (new.ware_id<0) then
if (new.WARE_ID = ''-1'') then
begin
select GOODNAME, GOODIZG, GOODCOUNTRY, GOODBARCODE from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg)
into new.SNAME, new.SIZG, new.SCOUNTRY, new.BCODE_IZG;
end
--<cf20110923
new.PART_ID = 0;
select ID from VALS where VTYPE = 0 and ALTTYPE = new.PART_TYPE and SVALUE = new.SNAME into new.NAME_ID;
select ID from VALS where VTYPE = 3 and ALTTYPE = new.PART_TYPE and SVALUE = new.SIZG into new.IZG_ID;
select ID from VALS where VTYPE = 2 and ALTTYPE = new.PART_TYPE and SVALUE = new.SCOUNTRY into new.COUNTRY_ID;
select ID from VALS where VTYPE = 1 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_NAME into new.ORIG_NAME_ID;
select ID from VALS where VTYPE = 6 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_IZG into new.ORIG_IZG_ID;
select ID from VALS where VTYPE = 5 and ALTTYPE = new.PART_TYPE and SVALUE = new.SORIG_COUNTRY into new.ORIG_COUNTRY_ID;
new.WARE_ID = 0;
select first 1 ID from WARES where NAME_ID = new.NAME_ID and IZG_ID = new.IZG_ID and COUNTRY_ID = new.COUNTRY_ID and BARCODE = new.BCODE_IZG into new.WARE_ID;
--вторая строчка = после приведения
insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
BARCODE1, BCODE_IZG,
GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
PART_TYPE, BASE_AGENT_ID)
values (new.ID, new.PARENT_ID, new.DOC_ID, new.PART_ID, new.PART_PARENT_ID, new.DOC_DETAIL_ID, new.PRICE, new.NAC,
new.QUANT, new.DISCOUNT, new.SUMMA, new.SUMMA_O, new.DCARD, new.WARE_ID, new.PRICE_O, new.PRICE_Z,
new.PRICE_R, new.BARCODE, new.BARCODE1, new.BCODE_IZG,
new.GODENDO, new.SERIA, new.NDS, new.SUM_NDSO, new.SERT, new.DATESERT, new.KEMVSERT, new.SDSERT, new.REGN,
new.NGTD, new.EDIZM, new.NAME_ID, new.IZG_ID, new.COUNTRY_ID, new.ORIG_CODE, new.ORIG_NAME_ID,
new.ORIG_IZG_ID, new.ORIG_COUNTRY_ID, new.Z_ID, new.SKLAD_ID, new.SNAME, new.SIZG, new.SCOUNTRY,
new.SORIG_NAME, new.SORIG_IZG, new.SORIG_COUNTRY, new.INSERTDT, new.INFO, new.KOEF, new.MOTHERPART_ID,
new.DEP, new.SUM_DSC, new.HUMAN_QUANT, new.CUSTOMDRAW, new.PART_TYPE, new.BASE_AGENT_ID);
--
end
if (new.PART_PARENT_ID is null) then
new.PART_PARENT_ID = 0;
if (new.WARE_ID is null) then
new.WARE_ID = 0;
if (new.NAME_ID is null) then
new.NAME_ID = 0;
if (new.IZG_ID is null) then
new.IZG_ID = 0;
if (new.COUNTRY_ID is null) then
new.COUNTRY_ID = 0;
if (new.ORIG_NAME_ID is null) then
new.ORIG_NAME_ID = 0;
if (new.ORIG_IZG_ID is null) then
new.ORIG_IZG_ID = 0;
if (new.ORIG_COUNTRY_ID is null) then
new.ORIG_COUNTRY_ID = 0;
if (new.Z_ID is null) then
new.Z_ID = 0;
if (new.SKLAD_ID is null) then
new.SKLAD_ID = '''';
if (new.MOTHERPART_ID is null) then
new.MOTHERPART_ID = 0;
if (new.DOC_DETAIL_ID is null) then
new.DOC_DETAIL_ID = 0;
if (new.PART_ID > 0) then -- заполняем значения партии
begin
select
WARE_ID,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
--QUANT,
BARCODE,
BARCODE1,
--KRITK,
GODENDO,
SERIA,
NDS,
--SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
nac,
dep,
part_type,
base_agent_id,
group_id,
MOTHERPART_UUID
from parts where id=new.part_id into
new.WARE_ID,
new.PRICE,
new.PRICE_O,
new.PRICE_Z,
new.PRICE_R,
--new.QUANT,
new.BARCODE,
new.BARCODE1,
--new.KRITK,
new.GODENDO,
new.SERIA,
new.NDS,
--new.SUM_NDSO,
new.SERT,
new.DATESERT,
new.KEMVSERT,
new.SDSERT,
new.REGN,
new.NGTD,
new.EDIZM,
new.nac,
new.dep,
new.part_type,
new.base_agent_id,
new.group_id,
new.MOTHERPART_UUID;
end
if (new.GROUP_ID is null) then
new.GROUP_ID = 0;
--if (new.ware_id>0) then -- заполняем значения позиции
if (new.WARE_ID not in (''-1'', ''0'')) then -- заполняем значения позиции
begin
select WW.NAME_ID,
(select SVALUE from VALS where ID = WW.NAME_ID), WW.IZG_ID,
(select SVALUE from VALS where ID = WW.IZG_ID), WW.COUNTRY_ID,
(select SVALUE from VALS where ID = WW.COUNTRY_ID), WW.ORIG_CODE, WW.ORIG_NAME_ID,
(select SVALUE from VALS where ID = WW.ORIG_NAME_ID), WW.ORIG_IZG_ID,
(select SVALUE from VALS where ID = WW.ORIG_IZG_ID), WW.ORIG_COUNTRY_ID,
(select SVALUE from VALS where ID = WW.ORIG_COUNTRY_ID), WW.BARCODE, WW.Z_ID, WW.SKLAD_ID from WARES WW where WW.ID = new.WARE_ID into new.NAME_ID,
new.SNAME, new.IZG_ID, new.SIZG, new.COUNTRY_ID, new.SCOUNTRY, new.ORIG_CODE, new.ORIG_NAME_ID, new.SORIG_NAME,
new.ORIG_IZG_ID, new.SORIG_IZG, new.ORIG_COUNTRY_ID, new.SORIG_COUNTRY, new.BCODE_IZG, new.Z_ID, new.SKLAD_ID;
end
else
begin
--if (new.ware_id<0) then
if (new.WARE_ID = ''-1'') then
begin
select GOODNAME, GOODIZG, GOODCOUNTRY from PR_MAKEGOODWAREVALUES(new.SNAME, new.SORIG_NAME, new.SIZG, new.SORIG_IZG, new.SCOUNTRY, new.SORIG_COUNTRY, new.ORIG_CODE, new.ID, new.PART_TYPE, new.bcode_izg) into new.SNAME,
new.SIZG, new.SCOUNTRY;
new.WARE_ID = 0;
end
end
if (new.quant is null) then new.quant=0;
if (new.price is null) then new.price=0;
if (new.price_o is null) then new.price_o=0;
if (new.nds is null) then new.nds=0;
if (new.summa is null) then new.summa=new.quant*new.price;
if (new.sum_dsc is null) then new.sum_dsc=0;
if (new.summa_o is null) then
begin
new.summa_o=new.quant*new.price_o;
end
else
begin
if (new.quant<>0) then
new.price_o=new.summa_o/new.quant;
end
--В любом случае перерасчитываем наценку, т.к. в автопереоценках идет вставка старой наценки А.К. 20151020
--if (((new.nac is null) or (new.nac=0)) and new.price_o<>0) then
if (new.price_o<>0) then
new.nac=(new.price-new.price_o)*100/new.price_o;
if (new.dep is null) then
begin
select first 1 id from deps where nds=new.nds order by id into new.dep;
end
if (new.dep is null) then
new.dep=0;
-- В любом случае перерасчитываем НДСы, т.к. при возврате могут отличаться на копейки А.К. 20151113
--if ( (new.sum_ndso is null) or (new.sum_ndso=0) ) then
new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
--if ( (new.sum_ndsr is null) or (new.sum_ndso=0) ) then
new.sum_ndsr=new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep));
--if ((new.barcode is null) or (trim(new.barcode)='''') or (new.part_id=0) ) then ---> 20130301-ann gen_barcode_by_name
if ((new.barcode is null) or (trim(new.barcode)='''')) then ---> 20150320 A.K.
begin
if ((select PARAM_VALUE from PR_GETPARAMVALUE(''GEN_BARCODE'',0))=0) then
select res_bcode from utpr_getchecksum_ean13(new.id) into new.barcode;
else select res_bcode from utpr_getchecksum_ean13((select l_id from vals where d$uuid=new.name_id),''88'') into new.barcode;
end
---> 20130301-ann gen_barcode_by_name
if (new.part_parent_id>0) then
begin
update doc_detail_active set
quant=quant-new.quant,
summa=summa-new.summa,
summa_o=summa_o-new.summa_o,
sum_ndso=sum_ndso-new.sum_ndso,
sum_ndsr=sum_ndsr-new.sum_ndsr
where id=new.part_parent_id;
execute procedure PR_COPY_MEMBERSHIP(''DOC_DETAIL_ACTIVE'',new.part_parent_id,new.id);
end
--Если указано наименование, но не указан ШК изготовителя - ищем его в партиях и подставляем по умолчанию 20.03.2015 A.K.
if (new.name_id is not null and (new.bcode_izg is null or Trim(new.bcode_izg) = '''' )
and exists(select first 1 id from docs where doc_type in (1,2,20) and id = new.doc_id)) then
--20151113 A.K.
new.bcode_izg = (select list(barcode,'','') from PR_GET_ALL_BARCODE(new.name_id));
-- select first 1 w.barcode from wares w where w.name_id = new.name_id into new.bcode_izg;
-- select first 1 w.barcode from wares w inner join vals v on w.name_id = v.id or w.orig_name_id = v.id
-- where w.barcode is not null and Trim(w.barcode) <> '''' and ( v.svalue =new.sname or v.svalue = new.sorig_name )
-- into new.bcode_izg;
--Для "Заказ оптовику" при отрицательном или нулевом кол-ве товара - берем кол-во из последнего прихода 02.03.2015 A.K.
if (((Select Doc_type From docs where id = new.doc_id) = 36) and new.quant <= 0 )
then
begin
new.quant = (
select first 1 dd.quant from doc_detail dd
inner join docs d on dd.doc_id = d.id
inner join parts p on dd.part_id=p.id
inner join WARES w on p.ware_id=w.id
where d.doc_type in (1,2) and w.name_id = new.name_id order by dd.insertdt desc );
if (new.quant is null or new.quant = 0) then new.quant = 1;
end
--Для "Заказ оптовику" цену и цену опт берем из остатков 19.10.2015 A.K.
if ((Select Doc_type From docs where id = new.doc_id) = 36) Then
Begin
select first 1 price, price_o, part_id,ware_id from warebase where name_id = new.name_id order by docdate desc
into new.price, new.price_o, new.part_id,new.ware_id;
end
--Заявка 656799 от 14.07.2016 A.K.
--Ткаченко: При добавлении позиции из остатков в активный документ прихода от поставщика розничную цену ставить 0, чтобы не забывали наценивать накладные
if ((Select Doc_type From docs where id = new.doc_id) = 1) Then
begin
new.part_id=0;
new.price=0;
new.nac=-100;
new.summa=0;
end
end
^
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BU0 FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE UPDATE POSITION 0
as
begin
if (new.nds<>old.nds) then --Изменение НДС только через отдел OlgaV 20141114
begin
select first 1 id from deps where status=0 and nds=new.nds into new.dep;
new.nds=old.nds;
end
if (new.dep<>old.dep) then
select nds from deps where id=new.dep into new.nds;
if ((new.nds<>old.nds) or (new.sum_ndso is null) or (new.sum_ndsr is null) ) then
begin
new.sum_ndso=round(new.summa_o*new.nds/(100+new.nds),2);
new.sum_ndsr=new.summa*(select ndsr from deps where id=new.dep)/(100+(select ndsr from deps where id=new.dep));
end --Пересчет сумм НДС Olgav20141114
if (abs(new.sum_ndso-round((new.quant*new.price_o)*new.nds/(100+new.nds),2))>0.05) then
new.sum_ndso=round((new.quant*new.price_o)*new.nds/(100+new.nds),2);
if (abs(new.sum_ndsr-(new.summa)*(100+(select d.ndsr from deps d where d.id=new.dep))/(100+(select d.ndsr from deps d where d.id=new.dep)))>0.05) Then
new.sum_ndsr=(new.summa)*(select d.ndsr from deps d where d.id=new.dep)/(100+(select d.ndsr from deps d where d.id=new.dep));
if ( (new.sname <> old.sname) or (new.name_id is null) or (new.name_id = ''0'') ) then
select val_id from pr_getval_id(new.sname,0,new.part_type,new.mnn) into new.name_id;
--select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
if ( (new.summa is null) and ((new.quant<>0) and (new.price<>0)) ) then
begin
new.summa=new.quant*new.price;
if (new.sum_dsc is not null) then new.summa = new.summa - new.sum_dsc;
end
if ( ((new.summa_o = 0) or (new.summa_o is null) ) and ((new.quant<>0) and (new.price_o<>0)) ) then
new.summa_o=new.quant*new.price_o;
--if (new.name_id <> old.name_id) then execute procedure PR_COPYGROUPS(old.name_id, new.name_id); 23072015 A.K.
if (new.sum_dsc is null) then
if (new.summa<>old.summa) then
begin
new.sum_dsc=(1+new.discount/100)*100*new.summa/(100+old.discount)-100*new.summa/(100+old.discount);
--new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
if (new.sum_dsc is null) then
if (new.discount<>old.discount) then
begin
new.sum_dsc=(1+new.discount/100)*100*old.summa/(100+old.discount)-100*old.summa/(100+old.discount);
new.summa=(1+new.discount/100)*100*old.summa/(100+old.discount);
end
select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id;
if (new.name_id is null) then new.name_id=0;
select id from vals where vtype=3 and alttype=new.part_type and svalue=new.sizg into new.izg_id;
if (new.izg_id is null) then new.izg_id=0;
select id from vals where vtype=2 and alttype=new.part_type and svalue=new.scountry into new.country_id;
if (new.country_id is null) then new.country_id=0;
select id from vals where vtype=1 and alttype=new.part_type and svalue=new.sorig_name into new.orig_name_id;
if (new.orig_name_id is null) then new.orig_name_id=0;
select id from vals where vtype=6 and alttype=new.part_type and svalue=new.sorig_izg into new.orig_izg_id;
if (new.orig_izg_id is null) then new.orig_izg_id=0;
select id from vals where vtype=5 and alttype=new.part_type and svalue=new.sorig_country into new.orig_country_id;
if (new.orig_country_id is null) then new.orig_country_id=0;
if ((old.part_id<>0) and (new.part_id=0) and (select deleted_doc_id from docs where id=new.doc_id) is null ) then
begin
if ((select PARAM_VALUE from PR_GETPARAMVALUE(''GEN_BARCODE'',0))=0) then
select res_bcode from utpr_getchecksum_ean13(new.id) into new.barcode;
else select res_bcode from utpr_getchecksum_ean13((select l_id from vals where d$uuid=new.name_id),''88'') into new.barcode;
end
if ((select PARAM_VALUE from pr_getparamvalue(''CHANGE_BRC_FOR_CORRECT'',''0'')) = ''0'') Then --Менять ШК в док-тах корректировки
if ((select d.doc_type from docs d where d.id=new.doc_id) in (7,8,22) and new.quant>0 and new.barcode<>'''' and new.barcode is not null) then
new.barcode=(select barcode from doc_detail_active where id=new.parent_id);
--Если указано наименование, но не указан ШК изготовителя - ищем его в партиях и подставляем по умолчанию 20.03.2015 A.K.
if (new.name_id is not null and (new.bcode_izg is null or Trim(new.bcode_izg) = '''' )
and exists(select first 1 id from docs where doc_type in (1,2,20) and id = new.doc_id)) then
select first 1 w.barcode from wares w where w.name_id = new.name_id into new.bcode_izg;
--Менять оптовую цену можно только в документах прихода от поставщика и оприходования (Заявка 574482 от 26.04.2016 А.К.)
if ( (coalesce(new.price_o,0) <> coalesce(old.price_o,0)) and ((select doc_type from docs where id = new.doc_id) not in (1,20)) ) then
exception EX_ZAPRET_PRICE_O;
exit; -- 20160424 A.K.
insert into DOC_DETAIL_ACTIVE_LOG (ID, PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT,
DISCOUNT, SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE,
BARCODE1, BCODE_IZG,
--KRITK,
GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM,
NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID,
Z_ID, SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY,
INSERTDT, INFO, KOEF, MOTHERPART_ID, DEP, SUM_DSC, HUMAN_QUANT, CUSTOMDRAW,
PART_TYPE, BASE_AGENT_ID)
values (old.ID, old.PARENT_ID, old.DOC_ID, old.PART_ID, old.PART_PARENT_ID, old.DOC_DETAIL_ID, old.PRICE, old.NAC,
old.QUANT, old.DISCOUNT, old.SUMMA, old.SUMMA_O, old.DCARD, old.WARE_ID, old.PRICE_O, old.PRICE_Z,
old.PRICE_R, old.BARCODE, old.BARCODE1, old.BCODE_IZG,
--old.KRITK,
old.GODENDO, old.SERIA, old.NDS, old.SUM_NDSO, old.SERT, old.DATESERT, old.KEMVSERT, old.SDSERT, old.REGN,
old.NGTD, old.EDIZM, old.NAME_ID, old.IZG_ID, old.COUNTRY_ID, old.ORIG_CODE, old.ORIG_NAME_ID,
old.ORIG_IZG_ID, old.ORIG_COUNTRY_ID, old.Z_ID, old.SKLAD_ID, old.SNAME, old.SIZG, old.SCOUNTRY,
old.SORIG_NAME, old.SORIG_IZG, old.SORIG_COUNTRY, old.INSERTDT, old.INFO, old.KOEF, old.MOTHERPART_ID,
old.DEP, old.SUM_DSC, old.HUMAN_QUANT, old.CUSTOMDRAW, old.PART_TYPE, old.BASE_AGENT_ID);
end
^
create or alter procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT (
ACTIVE_ID type of DM_ID,
COMMIT_ID type of DM_ID,
DOC_ID type of DM_ID,
COMMITDATE type of DM_DATETIME)
as
begin
/*
эта процедура выполняется для каждой записи, добавляемой в DOC_DETAIL в процедурах проведения документов
*/
/* Для вречей*/
update doc_detail_doctor set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
/* Старые рецепты*/
update DOC_DETAIL_MAKE set doc_detail_id=:commit_id, doc_id=:doc_id, commitdate=:commitdate where DOC_DETAIL_ACTIVE_ID=:active_id;
/* Новые рецепты*/
update recepts set table_id=:commit_id, table_name=''DOC_DETAIL'', status=1 where doc_detail_active_id=:active_id; --для рецептов
execute procedure PR_AUTO_WARESLINK(:DOC_ID, :ACTIVE_ID);
end;^
SET TERM ; ^
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,--(select svalue from vals where id = w.name_id),
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
;
/******************************************************************************/
/*** Generated by IBExpert 12.08.2016 17:39:43 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_WARES_TREB_ID;
CREATE TABLE WARES_TREB (
ID DM_ID NOT NULL /* DM_ID = BIGINT */,
WARE_ID DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
NAME_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
IZG_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
COUNTRY_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
ORIG_CODE DM_TEXT /* DM_TEXT = VARCHAR(250) */,
ORIG_NAME_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
ORIG_IZG_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
ORIG_COUNTRY_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
Z_ID DM_ID NOT NULL /* DM_ID = BIGINT */,
SNAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SIZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SCOUNTRY DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_NAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_IZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_COUNTRY DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SKLAD_ID DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
PRICE DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SUMMA DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SUMMA_O DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_O DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_Z DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_R DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
BARCODE DM_TEXT /* DM_TEXT = VARCHAR(250) */,
BARCODE1 DM_TEXT /* DM_TEXT = VARCHAR(250) */,
GODENDO DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
SERIA DM_TEXT /* DM_TEXT = VARCHAR(250) */,
NDS DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SUM_NDSO DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SERT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DATESERT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
KEMVSERT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SDSERT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
REGN DM_TEXT /* DM_TEXT = VARCHAR(250) */,
NGTD DM_TEXT /* DM_TEXT = VARCHAR(250) */,
EDIZM DM_TEXT /* DM_TEXT = VARCHAR(250) */,
INSERTDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
BCODE_IZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DDA_UUID DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
CMT_STATUS DM_STATUS /* DM_STATUS = INTEGER */,
CMT_DT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
CMT_USER DM_TEXT /* DM_TEXT = VARCHAR(250) */,
CMT_PROFILE_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */,
PART_TYPE DM_STATUS /* DM_STATUS = INTEGER */,
RQ_USER DM_TEXT /* DM_TEXT = VARCHAR(250) */,
RQ_PROFILE_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */,
D$SRVUPDDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
D$UUID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE WARES_TREB ADD CONSTRAINT PK_WARES_TREB PRIMARY KEY (D$UUID);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: WARES_TREB_AD_DISTR */
CREATE OR ALTER TRIGGER WARES_TREB_AD_DISTR FOR WARES_TREB
ACTIVE AFTER DELETE POSITION 0
AS
begin
update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values (''WARES_TREB'',old.d$uuid,2,null)
matching (TABLENAME,UUID);
end
^
/* Trigger: WARES_TREB_AU0 */
CREATE OR ALTER TRIGGER WARES_TREB_AU0 FOR WARES_TREB
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable name_id type of DM_UUID;
declare variable sname type of DM_TEXT;
declare variable izg_id type of DM_UUID;
declare variable sizg type of DM_TEXT;
declare variable country_id type of DM_UUID;
declare variable scountry type of DM_TEXT;
declare variable orig_code type of DM_TEXT;
declare variable orig_name_id type of DM_UUID;
declare variable sorig_name type of DM_TEXT;
declare variable orig_izg_id type of DM_UUID;
declare variable sorig_izg type of DM_TEXT;
declare variable orig_country_id type of DM_UUID;
declare variable sorig_country type of DM_TEXT;
declare variable barcode type of DM_TEXT;
begin
if (not exists (select d$uuid from doc_detail_active where d$uuid=new.dda_uuid)) then exit;
if (not exists (select d$uuid from wares where d$uuid=new.ware_id)) then exit;
select
w.name_id, (select svalue from vals v1 where v1.id=w.name_id),
w.izg_id, (select svalue from vals v2 where v2.id=w.izg_id),
w.country_id, (select svalue from vals v3 where v3.id=w.country_id),
w.orig_code,
w.orig_name_id, (select svalue from vals v4 where v4.id=w.orig_name_id),
w.orig_izg_id, (select svalue from vals v5 where v5.id=w.orig_izg_id),
w.orig_country_id, (select svalue from vals v6 where v6.id=w.orig_country_id),
w.barcode
from wares w where d$uuid=new.ware_id into 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;
update doc_detail_active set ware_id=new.ware_id,
name_id=:name_id,sname=:sname,
izg_id=:izg_id,sizg=:sizg,
country_id=:country_id,scountry=:scountry,
orig_code=:orig_code,
orig_name_id=:orig_name_id,sorig_name=:sorig_name,
orig_izg_id=:orig_izg_id,sorig_izg=:sorig_izg,
orig_country_id=:orig_country_id,sorig_country=:sorig_country,
bcode_izg=:barcode
where d$uuid=new.dda_uuid;
execute procedure PR_DOEVENT(''EV_WARES_TREB'');
end
^
/* Trigger: WARES_TREB_BI */
CREATE OR ALTER TRIGGER WARES_TREB_BI FOR WARES_TREB
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_wares_treb_id,1);
new.insertdt=current_timestamp;
if (new.CMT_STATUS is null) then
new.CMT_STATUS=0;
if (new.d$uuid is null) then --создание требования
begin
select null,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,Z_ID,
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
from doc_detail_active where d$uuid=new.dda_uuid into
new.WARE_ID,new.NAME_ID,new.IZG_ID,new.COUNTRY_ID,new.ORIG_CODE,new.ORIG_NAME_ID,new.ORIG_IZG_ID,new.ORIG_COUNTRY_ID,
new.Z_ID,new.SORIG_NAME,new.SORIG_IZG,new.SORIG_COUNTRY,new.SKLAD_ID,new.PRICE,new.QUANT,
new.SUMMA,new.SUMMA_O,new.PRICE_O,new.PRICE_Z,new.PRICE_R,new.BARCODE,new.BARCODE1,new.GODENDO,new.SERIA,new.NDS,new.SUM_NDSO,
new.SERT,new.DATESERT,new.KEMVSERT,new.SDSERT,new.REGN,new.NGTD,new.EDIZM,new.INSERTDT;
end
end
^
/* Trigger: WARES_TREB_BI_DISTR */
CREATE OR ALTER TRIGGER WARES_TREB_BI_DISTR FOR WARES_TREB
ACTIVE BEFORE INSERT POSITION 1000
AS
begin
if (new.d$uuid is null) then
begin
new.d$uuid=UUID_TO_CHAR(GEN_UUID());
new.d$srvupddt=''2000-01-01'';
update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values (''WARES_TREB'',new.d$uuid,0,null) matching
(TABLENAME,UUID);
end
end
^
/* Trigger: WARES_TREB_BU_DISTR */
CREATE OR ALTER TRIGGER WARES_TREB_BU_DISTR FOR WARES_TREB
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values (''WARES_TREB'',new.d$uuid,1,null)
matching (TABLENAME,UUID);
end
^
SET TERM ; ^
/******************************************************************************/
/******************************************************************************/
/*** 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)
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
from wares_treb
wt
left join wares w on wt.ware_id = w.id
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_AU0 FOR WARES
ACTIVE AFTER UPDATE POSITION 0
AS
begin
update warebase set updatedt=current_timestamp where ware_id=new.id;
if (not(new.D$SRVUPDDT=old.D$SRVUPDDT)) then
exit;
if ((old.name_id<>new.name_id) or
(old.izg_id<>new.izg_id) or
(old.country_id<>new.country_id) or
-- (old.orig_code<>new.orig_code) or
(old.orig_name_id<>new.orig_name_id) or
(old.orig_izg_id<>new.orig_izg_id) or
(old.orig_country_id<>new.orig_country_id) or
(old.barcode<>new.barcode) or
-- (old.z_id<>new.z_id) or
(old.sklad_id<>new.sklad_id)) then
begin
insert into wares_log
(
ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
BARCODE,
Z_ID,
SKLAD_ID,
INSERTDT,
PACKET
)
values
(
old.ID,
old.NAME_ID,
old.IZG_ID,
old.COUNTRY_ID,
old.ORIG_CODE,
old.ORIG_NAME_ID,
old.ORIG_IZG_ID,
old.ORIG_COUNTRY_ID,
old.BARCODE,
old.Z_ID,
old.SKLAD_ID,
old.INSERTDT,
old.PACKET
);
end
end
^
SET TERM ; ^', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348176, 0, 0, 'cowa_2_recur', 'execute procedure PR_COWA_01;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO G$TASKS_TMPL (ID, PROFILE_ID, TASK_TYPE, CAPTION, DATA, INSERTDT, SENDDT, ENDDT, ENDFLAG, G$PROFILE_ID, D$UUID, D$SRVUPDDT, CHECKPREVTASK)
VALUES (348177, 0, 0, 'cowa_3_recur', 'ALTER TRIGGER WARES_LOG_BU_DISTR ACTIVE;', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
COMMIT WORK;
