ЕГАИС постановка на баланс — различия между версиями
Материал из wiki.standart-n.ru
BeTePoK (обсуждение | вклад) (→изменить триггер DOC_DETAIL_ACTIVE_BI) |
BeTePoK (обсуждение | вклад) (→Выполнить скрипт) |
||
| Строка 140: | Строка 140: | ||
if (((MMBSH containing '-21={') or (MMBSH containing '-20={')) and ((PRODUCER_ID is not null) and (ALCCODE is not null))) then | if (((MMBSH containing '-21={') or (MMBSH containing '-20={')) and ((PRODUCER_ID is not null) and (ALCCODE is not null))) then | ||
begin | begin | ||
| − | --работа | + | --работа со складом + |
| − | if (:DOC_TYPE in (40, | + | if (:DOC_TYPE in (40,45,46)) then |
begin | begin | ||
| − | |||
select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT; | select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT; | ||
| Строка 155: | Строка 154: | ||
end | end | ||
| − | --работа | + | --работа со складом - |
| − | if (:DOC_TYPE in ( | + | if (:DOC_TYPE in (41,42,44)) then |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
begin | begin | ||
select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT; | select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT; | ||
| Строка 183: | Строка 166: | ||
else | else | ||
update EGAIS_SKLAD es set es.QUANTITY = :QUANT-new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE; | update EGAIS_SKLAD es set es.QUANTITY = :QUANT-new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE; | ||
| + | end | ||
| + | |||
| + | --работа c торговым залом + | ||
| + | if (:DOC_TYPE in (44,47)) then | ||
| + | begin | ||
select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT; | select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT; | ||
| Строка 196: | Строка 184: | ||
end | end | ||
| − | + | --работа с торговым залом - | |
| − | if (:DOC_TYPE in (45)) then | + | if (:DOC_TYPE in (43,45)) then |
begin | begin | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT; | select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT; | ||
| Строка 215: | Строка 193: | ||
begin | begin | ||
insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID) | insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID) | ||
| − | values (new.part_id, :SNAME, | + | values (new.part_id, :SNAME, new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID); |
end | end | ||
else | else | ||
| − | update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT | + | update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT+new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE; |
| + | |||
end | end | ||
end | end | ||
Версия 09:51, 10 октября 2016
Содержание
Скрипты для постановки на баланс
Установить процедуру PR_ALTER_VIEW
Заменить и запустить версию Менеджера
Менеджер выполнит некоторые скрипты уже в него заложенные
Для скриптов ниже актуальная версия Менеджера ManagerXP2_272_78 и выше
Выполнить скрипт
SET TERM ^ ;
execute procedure PR_ALTER_VIEW(''PARTS'',''EGAIS_REGID, EGAIS_QUANT, EGAIS_ALCCODE'',''PARTS_IDX_EGAIS_1'',''index'');^
execute procedure PR_ALTER_VIEW(''PARTS'',''EGAIS_REGID, EGAIS_BREGID, EGAIS_ALCCODE, EGAIS_QUANT'',''PARTS_IDX_EGAIS_2'',''index'');^
execute procedure PR_ALTER_VIEW(''EGAIS_DETAIL'',''DOC_DETAIL_ID'',''EGAIS_DETAIL_IDX1'',''index'');^
--Зкомментированные ниже строки - наброски для гурманов для тогочтобы внести в базы ТМСки по списанию пива и созданию документа перемещения в торговый зал
--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Списание пива'') is null) then
--execute statement ''INSERT INTO GROUPS (PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID) VALUES (-220, ''''Списание пива'''', --''''TMS'''', 0, ''''27-SEP-2016 16:24:48'''', 0, NULL, -1, NULL, 16777215, NULL, 0, NULL);'';
--END;^
--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Списание пива'') is null) then
--execute statement ''update groups gp set gp.data =
-- (select g.tmp_blob1 from G$PROFILES g where g.id = 1)
-- where gp.caption = ''''Списание пива'''' and (select g.tmp_blob1 from G$PROFILES g where g.id = 1) is not null;^'';
--END;^
--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Переместить в торговый зал'') is null) then
--execute statement ''
--INSERT INTO GROUPS (PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID) VALUES ( -221, 'Переместить в торговый зал', '', 0, '8-OCT-2016 12:04:29.200', 0, NULL, -1, NULL, 16777215, NULL, 0, NULL);'';
--END;^
--EXECUTE BLOCK AS BEGIN
--if ((select 1 from groups where caption = ''Переместить в торговый зал'') is null) then
--execute statement ''update groups gp set gp.data =
-- (select g.tmp_blob1 from G$PROFILES g where g.id = 3)
-- where gp.caption = ''''Переместить в торговый зал'''' and (select g.tmp_blob1 from G$PROFILES g where g.id = 3) is not null;^'';
--END;^
--поначалу не совсем понятно создавались наименования документов, тут правка на более понятные
update doc_types set caption=''ЕГАИС Списание со склада'' where id = 42;^
update doc_types set caption=''ЕГАИС Списание из торгового зала'' where id = 43;^
update doc_types set caption=''ЕГАИС Перемещение со склада в торговый зал'' where id = 44;^
update doc_types set caption=''ЕГАИС Перемещение из торгового зала на склад'' where id = 45;^
update doc_types set caption=''ЕГАИС Акт постановки на баланс'' where id = 46;^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_SKLAD_QUANT'',''(select esk.quantity from egais_sklad esk where esk.informbregid = p.egais_bregid)'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_SHOP_QUANT'',''(select esh.egais_quant from egais_shop esh where esh.alccode = p.egais_alccode and esh.producer_id = p.egais_producer_id)'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_REGID'',''p.egais_regid'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_WAREBASE'',''EGAIS_BREGID'',''p.egais_bregid'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''BOTTLINGDATE'',''BOTTLINGDATE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_BARCODE'',''EGAIS_BARCODE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_FIXNUMBER'',''EGAIS_FIXNUMBER'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_FIXDATE'',''EGAIS_FIXDATE'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_REGID'',''p.egais_regid'',''VIEW'');^
execute procedure PR_ALTER_VIEW(''VW_DOC_DETAIL_ACTIVE'',''EGAIS_BREGID'',''p.egais_bregid'',''VIEW'');^
--в начальных вариантах был неправилный домен для акцизы, после применения этого скрипта есть вероятность что нужно будет ждатьбэкап-ресторе, чтобы изменения применились
update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = ''DM_BLOBTEXT''
where (RDB$FIELD_NAME = ''EGAIS_BARCODE'') and
(RDB$RELATION_NAME = ''PARTS'');^
update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = ''DM_BLOBTEXT''
where (RDB$FIELD_NAME = ''EGAIS_BARCODE'') and
(RDB$RELATION_NAME = ''DOC_DETAIL_ACTIVE'');^
execute procedure PR_ALTER_VIEW('''','''',''GEN_EGAIS_SKLAD_ID'',''generator'');^
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = ''EGAIS_SKLAD'')) then
execute statement ''CREATE TABLE EGAIS_SKLAD(ID DM_ID NOT NULL);'';
END^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''QUANTITY'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMAREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMBREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''FULLNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''ALCCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''CAPACITY'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''ALCVOLUME'',''DM_DOUBLE'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCTVCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_CLIENTREGID'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_INN'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_KPP'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_FULLNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_SHORTNAME'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_COUNTRY'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_REGIONCODE'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''PRODUCER_DESCRIPTION'',''DM_TEXT1024'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INSERTDT'','' DM_DATETIME'',''TABLE'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMAREGID'',''EGAIS_SKLAD_IDX1'',''index'');^
execute procedure PR_ALTER_VIEW(''EGAIS_SKLAD'',''INFORMBREGID'',''EGAIS_SKLAD_IDX2'',''index'');^
CREATE OR ALTER TRIGGER EGAIS_SKLAD_BI FOR EGAIS_SKLAD
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_egais_sklad_id,1);
if (new.insertdt is null) then
new.insertdt = ''now'';
end
^
--изменения типа редактирования штрихкода на тип memo, в большинстве случаев есть в настройках менеджера, иногда просто скрыто
update params set param_value = '' 1'' where PARAM_CAPTION = ''BCODE_EDITOR'';^
--триггер слежения за актуальностью егаис остатков, в любом случае надо время от времени запрашивать их с егаиса
CREATE OR ALTER TRIGGER DOC_DETAIL_AI1 FOR DOC_DETAIL
ACTIVE AFTER INSERT POSITION 0
AS
DECLARE VARIABLE PRODUCER_ID DM_TEXT;
DECLARE VARIABLE ALCCODE DM_TEXT1024;
DECLARE VARIABLE QUANT DM_DOUBLE;
DECLARE VARIABLE MMBSH DM_TEXT1024;
DECLARE VARIABLE SNAME DM_TEXT;
DECLARE VARIABLE UNITTYPE DM_TEXT;
DECLARE VARIABLE ALCVOLUME DM_TEXT;
DECLARE VARIABLE PRODUCTVCODE DM_TEXT;
DECLARE VARIABLE DOC_TYPE DM_ID;
begin
/* учёт егаис остатков в реальном времени */
select doc_type from docs where id = new.doc_id into DOC_TYPE;
select first 1 MMBSH,SNAME from warebase where part_id = new.part_id into :MMBSH,:SNAME;
select EGAIS_PRODUCER_ID,EGAIS_ALCCODE,ALC_VOLUME,EGAIS_PRODUCT_VCODE from parts p where p.id = new.part_id into :PRODUCER_ID,:ALCCODE,:ALCVOLUME,:PRODUCTVCODE;
if (((MMBSH containing '-21={') or (MMBSH containing '-20={')) and ((PRODUCER_ID is not null) and (ALCCODE is not null))) then
begin
--работа со складом +
if (:DOC_TYPE in (40,45,46)) then
begin
select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT;
if (QUANT is null) then
begin
insert into EGAIS_SKLAD (QUANTITY,FULLNAME,ALCCODE,PRODUCER_CLIENTREGID,PRODUCTVCODE)
values (new.quant,:SNAME,:ALCCODE,:PRODUCER_ID,:PRODUCTVCODE);
end
else
update EGAIS_SKLAD es set es.QUANTITY = :QUANT+new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE;
end
--работа со складом -
if (:DOC_TYPE in (41,42,44)) then
begin
select QUANTITY from EGAIS_SKLAD es where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE into :QUANT;
if (QUANT is null) then
begin
insert into EGAIS_SKLAD (QUANTITY,FULLNAME,ALCCODE,PRODUCER_CLIENTREGID,PRODUCTVCODE)
values (-new.quant,:SNAME,:ALCCODE,:PRODUCER_ID,:PRODUCTVCODE);
end
else
update EGAIS_SKLAD es set es.QUANTITY = :QUANT-new.quant where es.PRODUCER_CLIENTREGID = :PRODUCER_ID and es.ALCCODE = :ALCCODE;
end
--работа c торговым залом +
if (:DOC_TYPE in (44,47)) then
begin
select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT;
if (QUANT is null) then
begin
insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID)
values (new.part_id, :SNAME, new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID);
end
else
update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT+new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE;
end
--работа с торговым залом -
if (:DOC_TYPE in (43,45)) then
begin
select EGAIS_QUANT from EGAIS_SHOP es where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE into :QUANT;
if (QUANT is null) then
begin
insert into EGAIS_SHOP (PART_ID,SNAME,EGAIS_QUANT,ALCCODE,UNITTYPE,ALCVOLUME,PRODUCTVCODE,PRODUCER_ID)
values (new.part_id, :SNAME, new.quant,:ALCCODE,:UNITTYPE,:ALCVOLUME,:PRODUCTVCODE,:PRODUCER_ID);
end
else
update EGAIS_SHOP es set es.EGAIS_QUANT = :QUANT+new.quant where es.producer_id = :PRODUCER_ID and es.alccode = :ALCCODE;
end
end
end
^
SET TERM ; ^
изменить триггер DOC_DETAIL_ACTIVE_BI
в той части триггера, где берутся данные из таблицы parts, добавить чтобы заполнялось поле EGAIS_PRODUCER_ID
