Установка ЕГАИС — различия между версиями

Версия 14:33, 10 декабря 2015

Заменить версию Менеджера

Заменить ManagerXP2.exe на версию 2.272.55 от декабря 2015 г. или более новую.

Выполнить скрипт

-создаёт и изменяет нужные таблица/триггеры и т.д.

SET SQL DIALECT 3;

ALTER TABLE DOC_DETAIL ADD FIO_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD FIO_BOLNOY_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD TYPE_LGOTA DM_ID_NULL;
ALTER TABLE DOC_DETAIL ADD RECEPT DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOC_DETAIL ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOCS ADD SUM_NDSR DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MOTHERPART_UUID DM_UUID_NULL;
ALTER TABLE PARTS ADD MOTHERPART_UUID DM_UUID_NULL;
ALTER TABLE VALS ADD D$UUID DM_UUID_NULL;
ALTER TABLE VALS ADD L_ID DM_ID_NULL;

ALTER TABLE AGENTS ADD EGAIS_ID DM_UUID_NULL;
ALTER TABLE DOCS ADD EGAIS_NUM DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_ID DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_UNITTYPE DM_TEXT;
ALTER TABLE DOCS ADD EGAIS_STATUS DM_STATUS;
DESCRIBE FIELD EGAIS_STATUS TABLE DOCS
'1 - Accept акт принятия
2 - Reject акт принятия
3 - Accept акт отказа
4 - Reject акт отказа
5 - Accept акт расхождения
6 - Reject акт расхождения';

ALTER TABLE PARTS ADD EGAIS_ID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_REGID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_BREGID DM_TEXT;
ALTER TABLE PARTS ADD EGAIS_ALCCODE DM_TEXT1024;
ALTER TABLE PARTS ADD EGAIS_QUANT DM_DOUBLE;
ALTER TABLE PARTS ADD CAPACITY DM_DOUBLE;
ALTER TABLE PARTS ADD ALC_VOLUME DM_DOUBLE;
ALTER TABLE PARTS ADD EGAIS_PRODUCER_ID DM_TEXT;

ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_BREGID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_REGID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_ALCCODE DM_TEXT1024;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_PRODUCT_VCODE DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD CAPACITY DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD ALC_VOLUME DM_DOUBLE;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_PRODUCER_ID DM_TEXT;
ALTER TABLE DOC_DETAIL_ACTIVE ADD EGAIS_QUANT DM_DOUBLE;

CREATE GENERATOR GEN_EGAIS_REQUESTS_ID;

CREATE TABLE EGAIS_REQUESTS (
    ID           DM_ID NOT NULL /* DM_ID = BIGINT */,
    REPLY_ID     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    REPLY_URL    DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    QUERY_TYPE   DM_STATUS /* DM_STATUS = INTEGER */,
    QUERY_URL    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    QUERY_DATA   DM_BLOBBIN /* DM_BLOBBIN = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    REPLY_DATA   DM_BLOBBIN /* DM_BLOBBIN = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    STATUS       DM_STATUS /* DM_STATUS = INTEGER */,
    INSERT_DATE  DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    REPLY_DATE   DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    EGAIS_ID     DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    QUERY_OUT    DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */
);

ALTER TABLE EGAIS_REQUESTS ADD CONSTRAINT PK_EGAIS_REQUESTS PRIMARY KEY (ID);
CREATE INDEX EGAIS_REQUESTS_IDX1 ON EGAIS_REQUESTS (REPLY_ID);

SET TERM ^ ;

/* Trigger: EGAIS_REQUESTS_BI */
CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BI FOR EGAIS_REQUESTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_egais_REQUESTS_id,1);
  if (new.insert_date is null) then
    new.insert_date = 'now';
end
^
SET TERM ; ^

SET TERM ^ ;
/* Trigger: EGAIS_REQUESTS_BU0 */
CREATE OR ALTER TRIGGER EGAIS_REQUESTS_BU0 FOR EGAIS_REQUESTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.reply_url <> '') then
    new.reply_date = 'now';
end
^
SET TERM ; ^

COMMENT ON COLUMN EGAIS_REQUESTS.QUERY_TYPE IS 
'1 - синхронизация контрагентов
2 - акт принятия ТТН
3 - акт отказа от ТТН
4 - акт расхождения по ТТН';

SET TERM ^ ;

create or alter procedure PR_NEW_PART (
    DOC_ID type of DM_ID,
    PARENT_ID type of DM_ID,
    WARE_ID type of DM_UUID_NULL,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    PRICE_Z type of DM_DOUBLE,
    PRICE_R type of DM_DOUBLE,
    QUANT type of DM_DOUBLE,
    BARCODE type of DM_TEXT,
    BARCODE1 type of DM_TEXT,
    DEP type of DM_ID,
    GODENDO type of DM_DATETIME,
    SERIA type of DM_TEXT,
    NDS type of DM_DOUBLE,
    SUM_NDSO type of DM_DOUBLE,
    SERT type of DM_TEXT,
    DATESERT type of DM_DATETIME,
    KEMVSERT type of DM_TEXT,
    SDSERT type of DM_DATETIME,
    REGN type of DM_TEXT,
    NGTD type of DM_TEXT,
    EDIZM type of DM_TEXT,
    NAC type of DM_DOUBLE,
    MOTHERPART_ID type of DM_ID,
    PART_TYPE DM_STATUS,
    BASE_AGENT_ID DM_ID,
    SKLAD_ID type of DM_TEXT,
    CONTRACT_ID DM_ID,
    DOC_DETAIL_ACTIVE_ID DM_ID,
    GROUP_ID DM_ID,
    MOTHERPART_UUID DM_UUID_NULL = 0,
    EGAIS_ID DM_ID_NULL = null,
    EGAIS_REGID DM_TEXT1024 = null,
    EGAIS_BREGID DM_TEXT1024 = null,
    EGAIS_ALCCODE DM_TEXT1024 = null,
    EGAIS_QUANT DM_DOUBLE = 0,
    CAPACITY DM_DOUBLE = 0,
    ALC_VOLUME DM_DOUBLE = 0,
    EGAIS_PRODUCER_ID DM_TEXT1024 = null)
returns (
    P_ID type of DM_ID)
as
begin
  p_id=gen_id(gen_parts_id,1);
  insert into parts
    (ID,parent_id,doc_id,WARE_ID,PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE,BARCODE1,DEP,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,
    EDIZM,NAC,motherpart_id,sklad_id,part_type,BASE_AGENT_ID,contract_id, doc_detail_active_id, group_id, motherpart_uuid,
    EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID)
  values
    (:p_id,:parent_id,:doc_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,
    :SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id,:sklad_id,:part_type,:BASE_AGENT_ID,:contract_id,:doc_detail_active_id, :group_id, :motherpart_uuid,
    :EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID);
  suspend;
end
^
SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT INSERT ON PARTS TO PROCEDURE PR_NEW_PART;

/* Existing privileges on this procedure */

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

SET TERM ^ ;

create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
    DOC_ID type of DM_ID,
    DOC_PARENT_ID type of DM_ID,
    PART_PARENT_ID type of DM_ID,
    DOC_COMMITDATE type of DM_DATE,
    CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_UUID_NULL;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable MNN DM_TEXT;
declare variable MOTHERPART_UUID DM_UUID_NULL;
declare variable EGAIS_ID DM_TEXT1024;
declare variable EGAIS_BREGID DM_TEXT1024;
declare variable EGAIS_REGID DM_TEXT1024;
declare variable EGAIS_ALCCODE DM_TEXT1024;
declare variable CAPACITY DM_DOUBLE;
declare variable ALC_VOLUME DM_DOUBLE;
declare variable EGAIS_PRODUCER_ID DM_TEXT1024;
declare variable EGAIS_QUANT DM_DOUBLE;
begin
for
select
/*ware_id*/  sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
/*part_id*/  PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
part_type, BASE_AGENT_ID, group_id, make_id, mnn, motherpart_uuid,
EGAIS_ID, EGAIS_BREGID, EGAIS_REGID, EGAIS_ALCCODE, CAPACITY, ALC_VOLUME, EGAIS_PRODUCER_ID, EGAIS_QUANT
from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
into
:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
:PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
:part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn, :motherpart_uuid,
:EGAIS_ID, :EGAIS_BREGID, :EGAIS_REGID, :EGAIS_ALCCODE, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID, :EGAIS_QUANT
do
begin
--    if ((price<0.001) or (price is null)) then exception EX_PRICE;
if ((price<0) or (price is null)) then exception EX_PRICE;
if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
--    if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
if (/*(SUM_NDSO<0) or */(SUM_NDSO is null)) then exception EX_SUM_NDSO;
select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
into :ware_id;
if ((part_id=0) or (part_id is NULL)) then
select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,
:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id,
:motherpart_uuid, :EGAIS_ID, :EGAIS_REGID, :EGAIS_BREGID, :EGAIS_ALCCODE, :EGAIS_QUANT, :CAPACITY, :ALC_VOLUME, :EGAIS_PRODUCER_ID) into :part_id;
execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
-- обновляем членство в группах
execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
-- присваиваем ценам партии
update prices set part_id=:part_id where dda_id=:active_id;
update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
end
end
^
SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDGROUPDETAIL_ACTIVE2PARTS TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT SELECT,INSERT,UPDATE ON PRICES TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;

/* Existing privileges on this procedure */

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

SET TERM ^ ;

create or alter procedure PR_GET_EGAIS_STATUS (
    ID DM_STATUS)
returns (
    OUT_TEXT DM_TEXT1024)
as
begin

  if ( (id is null) or (id = 0) )  then out_text = 'Не установлен';
  else if (id = 1) then out_text = 'Акт принятия принят';
  else if (id = 2) then out_text = 'Акт принятия отклонен';
  else if (id = 3) then out_text = 'Акт отказа принят';
  else if (id = 4) then out_text = 'Акт отказа отклонен';
  else if (id = 5) then out_text = 'Акт расхождений принят';
  else if (id = 6) then out_text = 'Акт расхождений отклонен';
  else out_text = 'Неизвестный статус';

  suspend;
end
^
SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_EGAIS_STATUS TO SYSDBA;

CREATE OR ALTER VIEW VW_DOC_DETAIL_ACTIVE(
    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,
    MMBSH,
    NDSBYDEP,
    REALQUANT,
    PRICE_DSC,
    BLOCK_QUANT,
    SUM_DSC,
    HUMAN_QUANT,
    CUSTOMDRAW,
    STATUS,
    OLDPRICE,
    PART_TYPE,
    PRICES,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    GNVLS,
    GROUP_ID,
    MAKE_ID,
    MNN,
    FIO,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE,
    SUM_NDSR,
    STORE_STRING,
    BARC_LABEL_COUNT,
    SGROUP_ID,
    MOTHERPART_UUID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE,
    EGAIS_PRODUCT_VCODE,
    CAPACITY,
    ALC_VOLUME,
    EGAIS_PRODUCER_ID,
    EGAIS_QUANT)
AS
select
da.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,
(select intvalue from vals where id=da.name_id),
GODENDO,
SERIA,
da.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,
da.INSERTDT,
INFO,
KOEF,
MOTHERPART_ID,
DEP,
(select membership from PR_MEMBERSHIPS('PARTS=' || da.part_id || ';DOC_DETAIL_ACTIVE=' || da.id || ';PARTS.NAME_ID=' || da.name_id || ';PARTS.IZG_ID=' ||da.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
deps.nds,
(select realquant from warebase w where w.part_id=da.part_id),
iif(da.quant between -0.000001 and 0.000001,0,cast(da.summa/da.quant as numeric(15,4))),
(select BLOCK_QUANT from warebase where part_id=da.part_id),
da.SUM_DSC,
da.human_quant,
DA.CUSTOMDRAW,
da.STATUS,
iif(da.parent_id<>0, (select ddd.price from DOC_DETAIL_ACTIVE ddd where ddd.id=da.parent_id),0),
part_type,
(select s from PR_GETPARTPRICES(da.part_id,da.id)),
da.BASE_AGENT_ID,
(select caption from agents where id=da.BASE_AGENT_ID),
da.gnvls,
da.group_id,
da.make_id,
da.mnn,
(select od.fio from DOC_DETAIL_DOCTOR docdoc left join out$traders od on docdoc.doctor_id=od.id where docdoc.doc_detail_active_id =da.id),
(select mgn_name from wares where id = da.ware_id) as mgn_name,
(select mgn_id from wares where id = da.ware_id) as mgn_id,
(select mgn_source from wares where id = da.ware_id) as mgn_source,
SUM_NDSR,
(select svalue from pr_getattribute('store_string', da.name_id)),
(select svalue from pr_getattribute('barc_label_count', da.name_id)),
(select gr.caption from groups gr where gr.id=da.group_id),
da.MOTHERPART_UUID,
EGAIS_ID,
EGAIS_BREGID,
EGAIS_REGID,
EGAIS_ALCCODE,
EGAIS_PRODUCT_VCODE,
CAPACITY,
ALC_VOLUME,
EGAIS_PRODUCER_ID,
EGAIS_QUANT
FROM
DOC_DETAIL_ACTIVE da left join deps on da.dep=deps.id
order by da.parent_id, da.sname, da.id;

CREATE OR ALTER VIEW VW_DOC_DETAIL(
    ID,
    PARENT_ID,
    DOC_ID,
    PART_ID,
    PART_PARENT_ID,
    PRICE,
    QUANT,
    DISCOUNT,
    SUMMA,
    SUMMA_O,
    DCARD,
    INSERTDT,
    WARE_ID,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    COUNTRY_ID,
    SCOUNTRY,
    ORIG_CODE,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    BCODE_IZG,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    BARCODE,
    BARCODE1,
    DEP,
    KRITK,
    GODENDO,
    SERIA,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    NDS,
    MMBSH,
    NDSBYDEP,
    BLOCK_QUANT,
    DOC_COMMITDATE,
    MOTHERPART_ID,
    HUMAN_QUANT,
    SUM_DSC,
    PART_TYPE,
    PACKET,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    CONTRACT_ID,
    NAC,
    VSHIFT,
    DEVICE_NUM,
    VNUM,
    DOC_STATUS,
    DOC_TYPE,
    MAKE_ID,
    FIO,
    GROUP_ID,
    MGN_NAME,
    MGN_ID,
    MGN_SOURCE,
    DOC_BASE_TYPE,
    SUM_NDSR,
    SKLAD_ID,
    STORE_STRING,
    BARC_LABEL_COUNT,
    RECEPT,
    FIO_ID,
    FIO_BOLNOY_ID,
    TYPE_LGOTA,
    MOTHERPART_UUID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE,
    CAPACITY,
    ALC_VOLUME,
    EGAIS_TTNID,
    EGAIS_QUANT)
AS
select
dt.ID,
dt.PARENT_ID,
dt.DOC_ID,
dt.PART_ID,
p.parent_id,
p.PRICE,
dt.QUANT,
dt.DISCOUNT,
dt.SUMMA,
dt.SUMMA_O,
dt.DCARD,
dt.INSERTDT,
p.WARE_ID,
w.name_id,
vname.svalue,
w.izg_id,
vizg.svalue,
w.country_id,
vcountry.svalue,
ORIG_CODE,
vorig_name.svalue,
vorig_izg.svalue,
vorig_country.svalue,
w.barcode,
p.PRICE_O,
p.PRICE_Z,
p.PRICE_R,
p.BARCODE,
p.BARCODE1,
p.DEP,
vname.intvalue,
p.GODENDO,
p.SERIA,
dt.SUM_NDSO,
p.SERT,
p.DATESERT,
p.KEMVSERT,
p.SDSERT,
p.REGN,
p.NGTD,
p.EDIZM,
p.NDS,
(select membership from PR_MEMBERSHIPS('PARTS=' || dt.part_id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.IZG_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
deps.nds,
(select BLOCK_QUANT from warebase where part_id=p.id),
DT.doc_commitdate,
p.motherpart_id,
dt.human_quant,
dt.SUM_DSC,
dt.part_type,
dt.packet,
dt.BASE_AGENT_ID,
(select caption from agents where id=dt.BASE_AGENT_ID),
p.contract_id,
DT.NAC,
d.vshift,
d.device_num,
d.vnum,
d.status,
d.doc_type,
dt.make_id,
(select od.fio from DOC_DETAIL_DOCTOR docdoc left join out$traders od on docdoc.doctor_id=od.id where docdoc.doc_detail_id =dt.id),
p.group_id,
w.mgn_name,
w.mgn_id,
w.mgn_source,
dts.base_type,
dt.SUM_NDSR,
p.SKLAD_ID,
(select svalue from pr_getattribute('store_string', w.name_id)),
(select svalue from pr_getattribute('barc_label_count', w.name_id)),
dt.RECEPT,
dt.FIO_ID,
dt.FIO_BOLNOY_ID,
dt.TYPE_LGOTA,
p.motherpart_uuid,
p.EGAIS_ID,
p.EGAIS_BREGID,
p.EGAIS_REGID,
p.EGAIS_ALCCODE,
p.CAPACITY,
p.ALC_VOLUME,
d.EGAIS_ID,
p.EGAIS_QUANT
from
doc_detail dt
left join parts p on dt.part_id=p.id
left join WARES w on p.ware_id=w.id
left join vals vname on w.name_id=vname.id
left join docs d on d.id=dt.doc_id
left join doc_types dts on d.doc_type = dts.id
left join vals vizg on w.izg_id=vizg.id
left join vals vcountry on w.country_id=vcountry.id
left join vals vorig_name on w.orig_name_id=vorig_name.id
left join vals vorig_izg on w.orig_izg_id=vorig_izg.id
left join vals vorig_country on w.orig_country_id=vorig_country.id
left join deps on p.dep=deps.id;

CREATE OR ALTER VIEW VW_PARTS(
    PART_ID,
    WARE_ID,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    COUNTRY_ID,
    SCOUNTRY,
    ORIG_CODE,
    SORIG_NAME,
    SORIG_IZG,
    SORIG_COUNTRY,
    BCODE_IZG,
    PRICE,
    PRICE_O,
    PRICE_Z,
    PRICE_R,
    QUANT,
    BARCODE,
    BARCODE1,
    DEP,
    GODENDO,
    SERIA,
    SUM_NDSO,
    SERT,
    DATESERT,
    KEMVSERT,
    SDSERT,
    REGN,
    NGTD,
    EDIZM,
    DOC_ID,
    INSERTDT,
    NDS,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    MNN,
    NAC,
    GROUP_ID,
    EGAIS_ID,
    EGAIS_BREGID,
    EGAIS_REGID,
    EGAIS_ALCCODE)
AS
select
p.ID,
p.WARE_ID,
w.name_id,
vname.svalue, --SNAME,
w.izg_id,
vizg.svalue, --SIZG,
w.country_id,
vcountry.svalue, --SCOUNTRY,
ORIG_CODE,
vorig_name.svalue, --Sorig_NAME,
vorig_izg.svalue, --Sorig_IZG,
vorig_country.svalue, --Sorig_COUNTRY,
w.barcode,-- BCODE_IZG,
p.PRICE,
p.PRICE_O,
p.PRICE_Z,
p.PRICE_R,
p.QUANT,
p.BARCODE,
p.BARCODE1,
p.DEP,
--p.KRITK,
p.GODENDO,
p.SERIA,
p.SUM_NDSO,
p.SERT,
p.DATESERT,
p.KEMVSERT,
p.SDSERT,
p.REGN,
p.NGTD,
p.EDIZM,
p.DOC_ID,
p.INSERTDT,
p.NDS,
p.BASE_AGENT_ID,
--(select caption from agents a1 where a1.id=p.BASE_AGENT_ID)
a1.caption,
vname.preparedvalue,
p.nac,
p.group_id,
p.EGAIS_ID,
p.EGAIS_BREGID,
p.EGAIS_REGID,
p.EGAIS_ALCCODE
from parts p left join WARES w on p.ware_id=w.id
  left join vals vname on w.name_id=vname.id
  left join vals vizg on w.izg_id=vizg.id
  left join vals vcountry on w.country_id=vcountry.id
  left join vals vorig_name on w.orig_name_id=vorig_name.id
  left join vals vorig_izg on w.orig_izg_id=vorig_izg.id
  left join vals vorig_country on w.orig_country_id=vorig_country.id
  left join agents a1 on p.base_agent_id=a1.id;

CREATE OR ALTER VIEW VW_DOCS(
    ID,
    PARENT_ID,
    DOC_TYPE,
    STATUS,
    AGENT_ID,
    DOCNUM,
    DOCDATE,
    CAPTION,
    RGUID,
    INSERTDT,
    POSTDT,
    AUDIT_ID,
    VNUM,
    VSHIFT,
    CREATER,
    OWNER,
    COMMITDATE,
    SDOC_TYPE,
    DOC_BASE_TYPE,
    SAGENT,
    SCREATER,
    SOWNER,
    CHECKDATA,
    SUMMA,
    SUMMA_O,
    SUM_NDSO,
    CALCSUMMA,
    SUMM1,
    SUMM2,
    SUMM3,
    SUMM4,
    COMMITSESSION_ID,
    DOC_VTYPE,
    CURRENCY_ID,
    CURRENCY_CAPTION,
    PRICE_TYPE,
    PRICE_TYPE_CAPTION,
    BASE_AGENT_ID,
    SBASE_AGENT_ID,
    CONTRACT_ID,
    LINK_ID,
    MAKE_ID,
    SUMMA_MAKE_CASH,
    COMMITDATE_TEXT,
    DEVICE_NUM,
    SELL_STATUS,
    DOC_PAY_DATE,
    CASH_DOC_PAYED,
    MMBSH,
    SUM_DSC,
    VIRTUAL_SUMMA,
    AGENT_CONTRACT_CAPTION,
    BASE_TYPE,
    SUM_NDSR,
    COMMENTS,
    EGAIS_NUM,
    EGAIS_ID,
    EGAIS_UNITTYPE,
    EGAIS_STATUS,
    EGAIS_STATUS_TEXT)
AS
select
docs.ID,
docs.PARENT_ID,
docs.DOC_TYPE,
docs.STATUS,
docs.AGENT_ID,
docs.DOCNUM,
docs.DOCDATE,
iif(docs.DOC_TYPE in (3,9),(select caption from doc_types where id=docs.DOC_TYPE)||' №'||iif(docs.VNUM is null,0,docs.VNUM),docs.CAPTION),
docs.RGUID,
docs.INSERTDT,
docs.POSTDT,
docs.AUDIT_ID,
docs.VNUM,
docs.VSHIFT,
docs.CREATER,
docs.OWNER,
docs.COMMITDATE,
dt.caption as sdoc_type,
dt.base_type as doc_base_type,
a.caption as sagent,
(select username from users where id=docs.creater) as screater,
(select username from users where id=docs.owner) as sowner,
docs.CHECKDATA,
iif(docs.doc_type=36 and docs.status in (0,2),(select sum(da.summa) from doc_detail_active da where da.doc_id=docs.id),docs.summa),
iif(docs.doc_type=36 and docs.status in (0,2),(select sum(da.summa_o) from doc_detail_active da where da.doc_id=docs.id),docs.summa_o),
docs.sum_ndso,
docs.CALCSUMMA,
docs.SUMM1,
docs.SUMM2,
docs.SUMM3,
docs.SUMM4,
docs.commitsession_id,
dt.vtype,
docs.currency_id,
(select caption from currencies where id=docs.currency_id),
docs.price_type,
(select caption from price_types where id=docs.price_type),
docs.BASE_AGENT_ID,
(select caption from agents where id=docs.BASE_AGENT_ID),
docs.contract_id,
docs.link_id,
docs.make_id,
(select abs(sum(cd.summa)) from cash_docs cd where cd.doc_id = docs.id and cd.status = 1),
cast(docs.commitdate as varchar(255)),
docs.DEVICE_NUM,
(select status from doc_hw where doc_id = docs.id and table_id = 0 and doc_type = 0),
docs.doc_pay_date,
(select sum(coalesce(summa,0)) from cash_doc_detail where doc_id = docs.id),
(select membership from PR_MEMBERSHIPS('CASH_DOCS=' || docs.id || ';',ascii_char(13)||ascii_char(10),1)),
docs.SUM_DSC,
iif(dt.base_type=1,summa_o,iif(dt.base_type=5,summ2,abs(docs.summa))),
ac.caption,
dt.base_type,
docs.SUM_NDSR,
docs.COMMENTS,
docs.EGAIS_NUM,
docs.EGAIS_ID,
docs.EGAIS_UNITTYPE,
docs.EGAIS_STATUS,
(select out_text from PR_GET_EGAIS_STATUS(docs.EGAIS_STATUS))
from
docs
left join doc_types dt on docs.doc_type=dt.id
left join agents a on docs.agent_id=a.id
left join sessions s on docs.audit_id=s.id
left join agents_contracts ac on docs.agents_contract_id = ac.id
where docs.id>0;

SET TERM ^ ;

create or alter procedure PR_GET_ADDRESS_ID (
    COUNTRY_ID DM_ID_NULL,
    REGION_ID DM_ID_NULL,
    DESCRIPTION DM_TEXT_BIG)
returns (
    ID DM_ID_NULL)
as
begin

  if ((select id from addr_vals where id = :country_id) is null) then
    insert into addr_vals (id, vtype, svalue) values (:country_id, 0, :country_id);
  if ((select id from addr_vals where id = :region_id) is null) then
    insert into addr_vals (id, vtype, svalue) values (:region_id, 1, :region_id);

  select id from addrs where country_id = :country_id and region_id = :region_id and addresstext = :description into :id;
  if (id is null) then
    insert into addrs (country_id, region_id, addresstext) values (:country_id, :region_id, :description) returning id into :id;

  suspend;
end
^
SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,INSERT ON ADDR_VALS TO PROCEDURE PR_GET_ADDRESS_ID;
GRANT SELECT,INSERT ON ADDRS TO PROCEDURE PR_GET_ADDRESS_ID;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_ADDRESS_ID TO SYSDBA;

INSERT INTO DOC_TYPES (ID, CAPTION, INSERTDT, BASE_TYPE, STATINI, REPORTS, VTYPE)
               VALUES (40, 'ТТН ЕГАИС', null, 1, '[XML]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.xml
askbefore=1

[freereport]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.cffr3

[dbf]
inipath=dbf.ini

[CSV]
inipath=dbf.ini
quotestrings=1
separator=#9
headers=1', ' [XML]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.xml
askbefore=1

[freereport]
enabled=1
caption=Печать пакета документов прихода
tmplpath=prihod.cffr

;''doc_id=:doc_id:'';''doc_detail=:doc_detail:''', 1);

INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID)
            VALUES (-10, -4, 'ЕГАИС оптовики', 'AGENTS', 0, null, 1, NULL, -1, NULL, NULL, NULL, 0);

Исправить триггер DOC_DETAIL_ACTIVE_BI

После условия  if (new.part_id>0) then -- заполняем значения партии , 
дополняем выборку из таблицы PARTS полями

..... motherpart_uuid, EGAIS_ID, EGAIS_REGID, EGAIS_BREGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME from parts where id=new.part_id into ...... new.motherpart_uuid, new.EGAIS_ID, new.EGAIS_REGID, new.EGAIS_BREGID, new.EGAIS_ALCCODE, new.EGAIS_QUANT, new.CAPACITY, new.ALC_VOLUME;

Настройка Менеджера

В Менеджере - сервис - параметры системы - Настройка ЕГАИС указать адрес сервера ЕГАИС и идентификатор

Сетки

сетка для активного документа егаисМедиа:Egais.txt