Установка ЕГАИС

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

Заменить 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 TRIGGER DOC_DETAIL_ACTIVE_BI FOR DOC_DETAIL_ACTIVE ACTIVE BEFORE INSERT POSITION 0 as begin 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; 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 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) into new.sname, new.sizg,new.scountry; end --<cf20110923 new.part_id=0; select val_id from PR_GETVAL_ID(new.sname, 0, new.part_type) into new.name_id; --select id from vals where vtype=0 and alttype=new.part_type and svalue=new.sname into new.name_id; select val_id from PR_GETVAL_ID(new.sizg, 3, new.part_type) into new.izg_id; --select id from vals where vtype=3 and alttype=new.part_type and svalue=new.sizg into new.izg_id; select val_id from PR_GETVAL_ID(new.scountry, 2, new.part_type) into new.country_id; --select id from vals where vtype=2 and alttype=new.part_type and svalue=new.scountry into new.country_id; select val_id from PR_GETVAL_ID(new.sorig_name, 1, new.part_type) into new.orig_name_id; --select id from vals where vtype=1 and alttype=new.part_type and svalue=new.sorig_name into new.orig_name_id; select val_id from PR_GETVAL_ID(new.sorig_izg, 6, new.part_type) into new.orig_izg_id; --select id from vals where vtype=6 and alttype=new.part_type and svalue=new.sorig_izg into new.orig_izg_id; select val_id from PR_GETVAL_ID(new.sorig_country, 5, new.part_type) into new.orig_country_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 orig_code=new.orig_code and orig_name_id=new.orig_name_id and orig_izg_id=new.orig_izg_id and orig_country_id=new.orig_country_id and barcode=new.bcode_izg into new.ware_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, EGAIS_ID, EGAIS_REGID, EGAIS_BREGID, EGAIS_ALCCODE, EGAIS_QUANT, CAPACITY, ALC_VOLUME 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, new.EGAIS_ID, new.EGAIS_REGID, new.EGAIS_BREGID, new.EGAIS_ALCCODE, new.EGAIS_QUANT, new.CAPACITY, new.ALC_VOLUME ; 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) 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 if (new.sum_ndso is null) then new.sum_ndso=new.summa_o*new.nds/100; if (((new.nac is null) or (new.nac=0)) and 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; ---> 20130301-ann gen_barcode_by_name if ((new.barcode is null) or (trim(new.barcode)='') or (new.part_id=0) ) 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(new.name_id,'88') 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 where id=new.part_parent_id; execute procedure PR_COPY_MEMBERSHIP('DOC_DETAIL_ACTIVE',new.part_parent_id,new.id); end end ^ SET TERM ; ^ 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);

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

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