/******************************************************************************/ /*** Generated by IBExpert 22.11.2019 12:34:51 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE WAREBASEPARTS ( PART_ID DM_ID NOT NULL /* DM_ID = BIGINT */, WARE_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */, SNAME DM_TEXT /* DM_TEXT = VARCHAR(250) */, SIZG DM_TEXT /* DM_TEXT = VARCHAR(250) */, SCOUNTRY DM_TEXT /* DM_TEXT = VARCHAR(250) */, ORIG_CODE 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) */, BCODE_IZG DM_TEXT /* DM_TEXT = VARCHAR(250) */, PRICE 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 */, QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, BARCODE DM_TEXT /* DM_TEXT = VARCHAR(250) */, BARCODE1 DM_TEXT /* DM_TEXT = VARCHAR(250) */, DEP DM_ID /* DM_ID = BIGINT */, KRITK DM_ID /* DM_ID = BIGINT */, GODENDO DM_DATETIME /* DM_DATETIME = TIMESTAMP */, SERIA DM_TEXT /* DM_TEXT = VARCHAR(250) */, 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) */, DOC_ID DM_ID /* DM_ID = BIGINT */, DOCNUM DM_TEXT /* DM_TEXT = VARCHAR(250) */, DOCDATE DM_DATETIME /* DM_DATETIME = TIMESTAMP */, DOCCAPTION DM_TEXT /* DM_TEXT = VARCHAR(250) */, DOCAGENT DM_TEXT /* DM_TEXT = VARCHAR(250) */, DOCVNUM DM_ID /* DM_ID = BIGINT */, DOCVSHIFT DM_ID /* DM_ID = BIGINT */, INSERTDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */, UPDATEDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */, ENDDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */, NDS DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, REALQUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, PART_PARENT_ID DM_ID /* DM_ID = BIGINT */, 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 */, NAC DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, BLOCK_QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, BLOCK_COUNT DM_STATUS /* DM_STATUS = INTEGER */, MMBSH DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */, PART_TYPE DM_STATUS /* DM_STATUS = INTEGER */, BASE_AGENT_ID DM_ID /* DM_ID = BIGINT */, SBASE_AGENT_ID DM_TEXT /* DM_TEXT = VARCHAR(250) */, CONTRACT_ID DM_ID /* DM_ID = BIGINT */, SCONTRACT_ID DM_TEXT /* DM_TEXT = VARCHAR(250) */, GROUP_ID DM_ID /* DM_ID = BIGINT */, ISFOLDER DM_STATUS /* DM_STATUS = INTEGER */, MNN DM_TEXT /* DM_TEXT = VARCHAR(250) */ ); /******************************************************************************/ /*** Primary keys ***/ /******************************************************************************/ ALTER TABLE WAREBASEPARTS ADD CONSTRAINT PK_WAREBASEPARTS PRIMARY KEY (PART_ID); /******************************************************************************/ /*** Indices ***/ /******************************************************************************/ CREATE INDEX WAREBASEPARTS_IDX1 ON WAREBASEPARTS (BCODE_IZG); CREATE INDEX WAREBASEPARTS_IDX2 ON WAREBASEPARTS (SNAME); CREATE INDEX WAREBASEPARTS_IDX3 ON WAREBASEPARTS (SERIA); CREATE INDEX WAREBASEPARTS_IDX4 ON WAREBASEPARTS (SORIG_NAME); CREATE INDEX WAREBASEPARTS_IDX5 ON WAREBASEPARTS (DOCAGENT); CREATE INDEX WAREBASEPARTS_IDX6 ON WAREBASEPARTS (DOCDATE); CREATE INDEX WAREBASEPARTS_IDX7 ON WAREBASEPARTS (NAME_ID); /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Views ***/ /******************************************************************************/ /* View: VW_WAREBASEPARTS */ CREATE OR ALTER VIEW VW_WAREBASEPARTS( PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN) AS select wb.PART_ID, wb.WARE_ID, wb.SNAME, wb.SIZG, wb.SCOUNTRY, wb.ORIG_CODE, wb.SORIG_NAME, wb.SORIG_IZG, wb.SORIG_COUNTRY, wb.BCODE_IZG, wb.PRICE, wb.PRICE_O, wb.PRICE_Z, wb.PRICE_R, w.QUANT, wb.BARCODE, wb.BARCODE1, wb.DEP, wb.KRITK, wb.GODENDO, wb.SERIA, wb.SUM_NDSO, wb.SERT, wb.DATESERT, wb.KEMVSERT, wb.SDSERT, wb.REGN, wb.NGTD, wb.EDIZM, wb.DOC_ID, wb.DOCNUM, wb.DOCDATE, wb.DOCCAPTION, wb.DOCAGENT, wb.DOCVNUM, wb.DOCVSHIFT, wb.INSERTDT, w.UPDATEDT, wb.ENDDT, wb.NDS, w.REALQUANT, wb.PART_PARENT_ID, wb.NAME_ID, wb.IZG_ID, wb.COUNTRY_ID, wb.NAC, w.BLOCK_QUANT, w.BLOCK_COUNT, wb.MMBSH, wb.PART_TYPE, wb.BASE_AGENT_ID, wb.SBASE_AGENT_ID, wb.CONTRACT_ID, wb.SCONTRACT_ID, wb.GROUP_ID, wb.ISFOLDER, wb.MNN from WAREBASEPARTS wb left join warebase w on w.part_id=wb.part_id ; /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ SET TERM ^ ; create or alter procedure UPDPR_WAREBASEPARTS ( IN_PART_ID DM_ID, IN_WARE_ID DM_UUID_NULL, IN_DOC_ID DM_ID, IN_AGENT_ID DM_ID) as declare variable PART_ID DM_ID not null; declare variable WARE_ID DM_UUID not null; declare variable SNAME DM_TEXT; declare variable SIZG DM_TEXT; declare variable SCOUNTRY DM_TEXT; declare variable ORIG_CODE DM_TEXT; declare variable SORIG_NAME DM_TEXT; declare variable SORIG_IZG DM_TEXT; declare variable SORIG_COUNTRY DM_TEXT; declare variable BCODE_IZG DM_TEXT; declare variable PRICE DM_DOUBLE; declare variable PRICE_O DM_DOUBLE; declare variable PRICE_Z DM_DOUBLE; declare variable PRICE_R DM_DOUBLE; declare variable QUANT DM_DOUBLE; declare variable BARCODE DM_TEXT; declare variable BARCODE1 DM_TEXT; declare variable DEP DM_ID; declare variable KRITK DM_ID; declare variable GODENDO DM_DATETIME; declare variable SERIA DM_TEXT; declare variable SUM_NDSO DM_DOUBLE; declare variable SERT DM_TEXT; declare variable DATESERT DM_DATETIME; declare variable KEMVSERT DM_TEXT; declare variable SDSERT DM_DATETIME; declare variable REGN DM_TEXT; declare variable NGTD DM_TEXT; declare variable EDIZM DM_TEXT; declare variable DOC_ID DM_ID; declare variable DOCNUM DM_TEXT; declare variable DOCDATE DM_DATETIME; declare variable DOCCAPTION DM_TEXT; declare variable DOCAGENT DM_TEXT; declare variable DOCVNUM DM_ID; declare variable DOCVSHIFT DM_ID; declare variable INSERTDT DM_DATETIME; declare variable UPDATEDT DM_DATETIME; declare variable ENDDT DM_DATETIME; declare variable NDS DM_DOUBLE; declare variable REALQUANT DM_DOUBLE; declare variable PART_PARENT_ID DM_ID; declare variable NAME_ID DM_UUID_NULL not null; declare variable IZG_ID DM_UUID_NULL not null; declare variable COUNTRY_ID DM_UUID_NULL not null; declare variable NAC DM_DOUBLE; declare variable BLOCK_QUANT DM_DOUBLE; declare variable BLOCK_COUNT DM_STATUS; declare variable MMBSH DM_TEXT1024; declare variable PART_TYPE DM_STATUS; declare variable BASE_AGENT_ID DM_ID; declare variable SBASE_AGENT_ID DM_TEXT; declare variable CONTRACT_ID DM_ID; declare variable SCONTRACT_ID DM_TEXT; declare variable GROUP_ID DM_ID; declare variable ISFOLDER DM_STATUS; declare variable MNN DM_TEXT; declare variable S DM_TEXT_BIG; begin s = 'select p.id, p.ware_id, vname.svalue, vizg.svalue, vcountry.svalue, w.orig_code, vorig_name.svalue, vorig_izg.svalue, vorig_country.svalue, w.barcode, p.price, p.price_o, p.price_z, p.price_r, 0, p.barcode, p.barcode1, p.dep, (select intvalue from vals where id=w.name_id), p.godendo, p.seria, p.sum_ndso, p.sert, p.datesert, p.kemvsert, p.sdsert, p.regn, p.ngtd, p.edizm, p.doc_id, d.docnum, d.docdate, d.caption, a.caption, d.vnum, d.vshift, p.insertdt, Null, p.enddt, p.nds, 0, p.parent_id, w.name_id, w.izg_id, w.country_id, p.nac, 0, 0, (select membership from PR_MEMBERSHIPS(''PARTS='' || p.id || '';PARTS.NAME_ID='' || w.name_id || '';PARTS.IZG_ID='' || w.izg_id ||'';'',ascii_char(13)||ascii_char(10),1)),--mmbsh, p.part_type, p.base_agent_id, (select ab.caption from agents ab where ab.id=p.BASE_AGENT_ID), p.contract_id, (select caption from contracts c where c.id=p.contract_id), --scontract_id, p.group_id, 1, vname.preparedvalue from parts p inner join WARES w on p.ware_id=w.id inner join docs d on p.doc_id=d.id inner join agents a on a.id = d.agent_id inner join vals vname on w.name_id=vname.id inner join vals vizg on w.izg_id=vizg.id inner 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 warebase wb on p.id=wb.part_id where (p.id>0) '; if (:IN_PART_ID is not null) then s = s || ' and p.id =' || :IN_PART_ID; if (:IN_WARE_ID is not null) then s = s || ' and p.ware_id =''' || :IN_WARE_ID||''''; if (:IN_DOC_ID is not null) then s = s || ' and p.doc_id =' || :IN_DOC_ID; if (:IN_AGENT_ID is not null) then s = s || ' and d.agent_id =' || :IN_AGENT_ID; for EXECUTE STATEMENT :s into :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN do begin update or insert into warebaseparts ( PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN ) values ( :PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN ) matching (part_id); end end^ SET TERM ; ^ /* Следующие операторы GRANT сгенерированы автоматически */ GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS; /* Существующие привилегии на эту процедуру */ GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA; SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER DOCS_WP_AIU0 FOR DOCS INACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin execute procedure updpr_warebaseparts(null, null, new.id, null); end ^ SET TERM ; ^ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER PARTS_WP_AIU0 FOR PARTS ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin execute procedure updpr_warebaseparts(new.id,null,null,null); end ^ SET TERM ; ^ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER WARES_WP_AIU0 FOR WARES ACTIVE AFTER INSERT OR UPDATE POSITION 0 AS begin if ((old.name_id<>new.name_id) or (old.izg_id<>new.izg_id) or (old.country_id<>new.country_id) 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)) Then execute procedure updpr_warebaseparts(null,new.id,null,null); end ^ SET TERM ; ^