Ускорение режима все партии — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(Новая страница: «/******************************************************************************/ →‎** Generated by IBExpert 04.03.2016 16:16:39 **: …»)
 
(→‎Для быстрого режима в инвентаризации (исправление VW_PARTSINV))
 
(не показано 20 промежуточных версии 3 участников)
Строка 1: Строка 1:
 +
=Выполнить скрипт=
 +
 +
<pre>
 +
 
/******************************************************************************/
 
/******************************************************************************/
−
/***              Generated by IBExpert 04.03.2016 16:16:39               ***/
+
/***              Generated by IBExpert 08.03.2016 11:37:13               ***/
 
/******************************************************************************/
 
/******************************************************************************/
  
Строка 17: Строка 21:
  
 
CREATE TABLE WAREBASEPARTS (
 
CREATE TABLE WAREBASEPARTS (
−
     PART_ID        BIGINT NOT NULL,
+
     PART_ID        DM_ID NOT NULL /* DM_ID = BIGINT */,
−
     WARE_ID        CHAR(36) NOT NULL,
+
     WARE_ID        DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
−
     SNAME          VARCHAR(250),
+
     SNAME          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SIZG            VARCHAR(250),
+
     SIZG            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SCOUNTRY        VARCHAR(250),
+
     SCOUNTRY        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     ORIG_CODE      VARCHAR(250),
+
     ORIG_CODE      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SORIG_NAME      VARCHAR(250),
+
     SORIG_NAME      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SORIG_IZG      VARCHAR(250),
+
     SORIG_IZG      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SORIG_COUNTRY  VARCHAR(250),
+
     SORIG_COUNTRY  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     BCODE_IZG      VARCHAR(250),
+
     BCODE_IZG      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     PRICE          DOUBLE PRECISION,
+
     PRICE          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     PRICE_O        DOUBLE PRECISION,
+
     PRICE_O        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     PRICE_Z        DOUBLE PRECISION,
+
     PRICE_Z        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     PRICE_R        DOUBLE PRECISION,
+
     PRICE_R        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     QUANT          DOUBLE PRECISION,
+
     QUANT          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     BARCODE        VARCHAR(250),
+
     BARCODE        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     BARCODE1        VARCHAR(250),
+
     BARCODE1        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DEP            BIGINT,
+
     DEP            DM_ID /* DM_ID = BIGINT */,
−
     KRITK          BIGINT,
+
     KRITK          DM_ID /* DM_ID = BIGINT */,
−
     GODENDO        TIMESTAMP,
+
     GODENDO        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     SERIA          VARCHAR(250),
+
     SERIA          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SUM_NDSO        DOUBLE PRECISION,
+
     SUM_NDSO        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     SERT            VARCHAR(250),
+
     SERT            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DATESERT        TIMESTAMP,
+
     DATESERT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     KEMVSERT        VARCHAR(250),
+
     KEMVSERT        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     SDSERT          TIMESTAMP,
+
     SDSERT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     REGN            VARCHAR(250),
+
     REGN            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     NGTD            VARCHAR(250),
+
     NGTD            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     EDIZM          VARCHAR(250),
+
     EDIZM          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DOC_ID          BIGINT,
+
     DOC_ID          DM_ID /* DM_ID = BIGINT */,
−
     DOCNUM          VARCHAR(250),
+
     DOCNUM          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DOCDATE        TIMESTAMP,
+
     DOCDATE        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     DOCCAPTION      VARCHAR(250),
+
     DOCCAPTION      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DOCAGENT        VARCHAR(250),
+
     DOCAGENT        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     DOCVNUM        BIGINT,
+
     DOCVNUM        DM_ID /* DM_ID = BIGINT */,
−
     DOCVSHIFT      BIGINT,
+
     DOCVSHIFT      DM_ID /* DM_ID = BIGINT */,
−
     INSERTDT        TIMESTAMP,
+
     INSERTDT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     UPDATEDT        TIMESTAMP,
+
     UPDATEDT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     ENDDT          TIMESTAMP,
+
     ENDDT          DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
−
     NDS            DOUBLE PRECISION,
+
     NDS            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     REALQUANT      DOUBLE PRECISION,
+
     REALQUANT      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     PART_PARENT_ID  BIGINT,
+
     PART_PARENT_ID  DM_ID /* DM_ID = BIGINT */,
−
     NAME_ID        CHAR(36),
+
     NAME_ID        DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
−
     IZG_ID          CHAR(36),
+
     IZG_ID          DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
−
     COUNTRY_ID      CHAR(36),
+
     COUNTRY_ID      DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
−
     NAC            DOUBLE PRECISION,
+
     NAC            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     BLOCK_QUANT    DOUBLE PRECISION,
+
     BLOCK_QUANT    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
−
     BLOCK_COUNT    INTEGER,
+
     BLOCK_COUNT    DM_STATUS /* DM_STATUS = INTEGER */,
−
     MMBSH          VARCHAR(1024),
+
     MMBSH          DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
−
     PART_TYPE      INTEGER,
+
     PART_TYPE      DM_STATUS /* DM_STATUS = INTEGER */,
−
     BASE_AGENT_ID  BIGINT,
+
     BASE_AGENT_ID  DM_ID /* DM_ID = BIGINT */,
−
     SBASE_AGENT_ID  VARCHAR(250),
+
     SBASE_AGENT_ID  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     CONTRACT_ID    BIGINT,
+
     CONTRACT_ID    DM_ID /* DM_ID = BIGINT */,
−
     SCONTRACT_ID    VARCHAR(250),
+
     SCONTRACT_ID    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
−
     GROUP_ID        BIGINT,
+
     GROUP_ID        DM_ID /* DM_ID = BIGINT */,
−
     ISFOLDER        INTEGER,
+
     ISFOLDER        DM_STATUS /* DM_STATUS = INTEGER */,
−
     MNN            VARCHAR(250)
+
     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);
 +
 +
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      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,
 +
    (select membership from PR_MEMBERSHIPS('PARTS=' || wb.part_id || ';PARTS.NAME_ID=' || wb.name_id || ';PARTS.IZG_ID=' || wb.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
 +
    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
 +
;
  
  
Строка 82: Строка 242:
 
/***                              Privileges                              ***/
 
/***                              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
 +
      inner join vals vorig_name on w.orig_name_id=vorig_name.id
 +
      inner join vals vorig_izg on w.orig_izg_id=vorig_izg.id
 +
      inner 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 ; ^
 +
 +
/* Following GRANT statetements are generated automatically */
 +
 +
GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;
 +
 +
SET SQL DIALECT 3;
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
CREATE OR ALTER TRIGGER AGENTS_WP_AIU0 FOR AGENTS
 +
ACTIVE AFTER INSERT OR UPDATE POSITION 0
 +
AS
 +
begin
 +
execute procedure updpr_warebaseparts(null, null, null, new.id);
 +
end
 +
^
 +
 +
 +
SET TERM ; ^
 +
 +
 +
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 ; ^
 +
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
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 ; ^
 +
 +
 +
execute procedure updpr_warebaseparts(null,null,null,null);
 +
 +
update selects s set s.sqltext='select wb.* from VW_WAREBASEPARTS wb where 1=1 order by sname' where s.id=-6;
 +
</pre>
 +
 +
='''Для быстрого режима в инвентаризации (исправление VW_PARTSINV)'''=
 +
 +
<pre>
 +
CREATE INDEX WAREBASEPARTS_IDX6 ON WAREBASEPARTS (PART_ID,SNAME);
 +
CREATE INDEX WAREBASEPARTS_IDX7 ON WAREBASEPARTS (sname,part_id);
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Views                                  ***/
 +
/******************************************************************************/
 +
 +
 +
/* View: VW_PARTSINV */
 +
CREATE OR ALTER VIEW VW_PARTSINV(
 +
    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,
 +
    QUANT2)
 +
AS
 +
select
 +
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,
 +
(select w.quant from warebase w where w.part_id=p.part_id)
 +
from warebaseparts p
 +
/* 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  */
 +
;
 +
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
 +
</pre>

Текущая версия на 10:18, 26 апреля 2017

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


/******************************************************************************/
/***               Generated by IBExpert 08.03.2016 11:37:13                ***/
/******************************************************************************/

/******************************************************************************/
/***      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);


/******************************************************************************/

/******************************************************************************/
/***      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,
    (select membership from PR_MEMBERSHIPS('PARTS=' || wb.part_id || ';PARTS.NAME_ID=' || wb.name_id || ';PARTS.IZG_ID=' || wb.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
    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
       inner join vals vorig_name on w.orig_name_id=vorig_name.id
       inner join vals vorig_izg on w.orig_izg_id=vorig_izg.id
       inner 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 ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER AGENTS_WP_AIU0 FOR AGENTS
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
 execute procedure updpr_warebaseparts(null, null, null, new.id);
end
^


SET TERM ; ^


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 ; ^


/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
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 ; ^


execute procedure updpr_warebaseparts(null,null,null,null);

update selects s set s.sqltext='select wb.* from VW_WAREBASEPARTS wb where 1=1 order by sname' where s.id=-6;

Для быстрого режима в инвентаризации (исправление VW_PARTSINV)

CREATE INDEX WAREBASEPARTS_IDX6 ON WAREBASEPARTS (PART_ID,SNAME);
CREATE INDEX WAREBASEPARTS_IDX7 ON WAREBASEPARTS (sname,part_id);
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_PARTSINV */
CREATE OR ALTER VIEW VW_PARTSINV(
    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,
    QUANT2)
AS
select
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,
(select w.quant from warebase w where w.part_id=p.part_id)
from warebaseparts p
/* 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   */
;




/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
Источник — «http://wiki.standart-n.ru/index.php?title=Ускорение_режима_все_партии&oldid=5876»