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

Версия 11:46, 21 июля 2016

set term ^;

CREATE TABLE EGAIS_PRODUCERS (
    ID           DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    INN          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    KPP          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    FULLNAME     DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    SHORTNAME    DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    COUNTRY      DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    POSTINDEX    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    REGIONCODE   DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    DESCRIPTION  DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    CITY         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    STREET       DM_TEXT /* DM_TEXT = VARCHAR(250) */
);^

CREATE UNIQUE INDEX EGAIS_PRODUCERS_IDX1 ON EGAIS_PRODUCERS (ID);^

create generator GEN_EGAIS_SHOP_ID;
^

create table EGAIS_SHOP (ID integer not null, PART_ID DM_ID_NULL / DM_ID_NULL = bigint /, SNAME DM_TEXT1024 / DM_TEXT1024 = varchar (1024) /, QUANT DM_DOUBLE / DM_DOUBLE = double precision /, EGAIS_QUANT DM_DOUBLE / DM_DOUBLE = double precision /, ALCCODE DM_TEXT1024 / DM_TEXT1024 = varchar (1024) /, UNITTYPE DM_TEXT / DM_TEXT = varchar (250) /, ALCVOLUME DM_DOUBLE / DM_DOUBLE = double precision /, PRODUCTVCODE DM_STATUS / DM_STATUS = integer /, PRODUCER_ID DM_TEXT / DM_TEXT = varchar (250) /);
^

create or alter trigger EGAIS_SHOP_BI for EGAIS_SHOP
active before insert position 0
as
begin
 if (new.ID is null) then
  new.ID = gen_id(GEN_EGAIS_SHOP_ID, 1);
end^

create or alter view VW_WARESHOP (
    ID,
    PART_ID,
    SNAME,
    QUANT,
    EGAIS_QUANT,
    ALCCODE,
    UNITTYPE,
    ALCVOLUME,
    PRODUCTVCODE,
    PRODUCER_ID)
as
select ID,
       PART_ID,
       SNAME,
       QUANT,
       EGAIS_QUANT,
       ALCCODE,
       UNITTYPE,
       ALCVOLUME,
       PRODUCTVCODE,
       PRODUCER_ID
from EGAIS_SHOP;
^

set term;
^