execute block (t1 timestamp=:t1, t2 timestamp=:t2)
returns (key_value varchar(255), namef varchar(50), classif_name varchar(512), nai varchar(120), date_time timestamp)
as
declare variable ids blob sub_type 1;
declare variable v varchar(255);
begin
ids=';';
for
    select key_value
    from V_LOG_TABLES
    where (table_name='ZAYAVL'   AND  operation='D')
  AND table_name='ZAYAVL'  and (date_time between :t1 and :t2) AND  operation='I'
    into :v
do ids=ids||v||';';
for
select Key_value, (select result from get_fio_from_tab('persons',pe.ke)) NAMEF , CLASSIF_NAME , NAI ,  date_time
from V_LOG_TABLES  vl
join zayavl za on za.ke=vl.key_value
join persons pe on  pe.login=vl.user_name
join usluga mu on mu.ke=za.usluga
join face fa on fa.ke=za.face
where position(';'||key_value||';' in :ids)=0
into :key_value,:namef,:classif_name,:nai,:date_time
do
suspend;
end;

CREATE OR ALTER VIEWV_LOG_TABLES(
    ID_OPERATION,
    KEY_VALUE,
    TABLE_NAME,
    OPERATION,
    DATE_TIME,
    USER_NAME,
    OLD_VALUE,
    NEW_VALUE,
    FIELD_NAME)
AS
select lt.id,lk.key_value,lt.table_name,lt.operation,lt.date_time,lt.user_name,lb.old_blob_value old_value,lb.new_blob_value new_value, lb.field_name
from ibe$log_tables lt
join IBE$LOG_BLOB_FIELDS  lb on lb.log_tables_id=lt.id
join ibe$log_keys lk on lk.log_tables_id=lt.id
where (old_blob_value is not null and new_blob_value is not null)
union
select lt.id,lk.key_value,lt.table_name,lt.operation,lt.date_time,lt.user_name,lf.old_value,lf.new_value, lf.field_name
from ibe$log_tables lt
join IBE$LOG_FIELDS lf on   lf.log_tables_id=lt.id
join ibe$log_keys lk on lk.log_tables_id=lt.id
;

begin
  RESULT='';
  short=0;
  IF (KE IS NOT NULL) THEN
  BEGIN
      S = 'SELECT COUNT(*) FROM '||TABLE_NAME||' WHERE KE='||:KE;
      EXECUTE STATEMENT S INTO :N;
      IF (N > 0) THEN
      BEGIN
          S = 'SELECT NAMEF,NAMEI,NAMEO FROM '||TABLE_NAME||' WHERE KE='||:KE;
          EXECUTE STATEMENT S INTO :TEMP1,:TEMP2,:TEMP3;
          if (temp1 is not null) then
          begin
            result=temp1;
            if (temp2 is not null) then
            begin
              if (short=0) then
                result=result||' '||temp2;
              else
                result= upper(substring(temp2 from 1 for 1))||'.';
            end
            if (temp3 is not null) then
            begin
              if (short=0) then
                result=result||' '||temp3;
              else
                result= upper(substring(temp3 from 1 for 1))||'.';
            end
          end
       end
   end
  suspend;
end

CREATE TABLE ZAYAVL (
    KE        INTEGER NOT NULL,
    NUM       VARCHAR(20),
    DOC_DATE  TIMESTAMP,
    ANNOTAT   VARCHAR(1024),
    MEMO      VARCHAR(1024),
    FACE      INTEGER,
    USLUGA    INTEGER,
    PARENT    INTEGER,
    DELEGATE  VARCHAR(1024)
);
ALTER TABLE ZAYAVL ADD CONSTRAINT PK_ZAYAVL PRIMARY KEY (KE);
ALTER TABLE ZAYAVL ADD CONSTRAINT FK_ZAYA_REFERENCE_FACE FOREIGN KEY (FACE) REFERENCES FACE (KE);
ALTER TABLE ZAYAVL ADD CONSTRAINT FK_ZAYA_REFERENCE_USLU FOREIGN KEY (USLUGA) REFERENCES USLUGA (KE);
ALTER TABLE ZAYAVL ADD CONSTRAINT FK_ZAYA_REFERENCE_ZAYA2 FOREIGN KEY (PARENT) REFERENCES ZAYAVL (KE);

CREATE TABLE PERSONS (
    KE           INTEGER NOT NULL,
    NAMEF        VARCHAR(30),
    NAMEI        VARCHAR(30),
    NAMEO        VARCHAR(30),
    PERSON_ROLE  INTEGER,
    ISACTIVE     BOOLEAN_DOMAIN /* BOOLEAN_DOMAIN = SMALLINT check (value is null or (value between 0 and 1)) */,
    STATUS       VARCHAR(256),
    LOGIN        CHAR(15)
);
ALTER TABLE PERSONS ADD CONSTRAINT CKC_ISACTIVE_PERSONS check (ISACTIVE is null or (ISACTIVE between 0 and 1));
ALTER TABLE PERSONS ADD CONSTRAINT PK_PERSONS PRIMARY KEY (KE);
ALTER TABLE PERSONS ADD CONSTRAINT FK_PERSONS_REFERENCE_ROLES FOREIGN KEY (PERSON_ROLE) REFERENCES ROLES (KE);

CREATE TABLE USLUGA (
    KE             INTEGER NOT NULL,
    NUM            INTEGER,
    CLASSIF_NAME   VARCHAR(512),
    REGLAMENT      BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    SHABLON        BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    POLUCHATEL     VARCHAR(512),
    PRICE          VARCHAR(512),
    PERIOD         INTEGER,
    RESULT         VARCHAR(512),
    NPA            VARCHAR(4096),
    DELETED        INTEGER,
    DUE            VARCHAR(64),
    DOGROUP_INDEX  VARCHAR(20),
    DEPARTMENT     INTEGER,
    DESCRIPTION    VARCHAR(1024),
    CLASSIF        VARCHAR(80)
);
ALTER TABLE USLUGA ADD CONSTRAINT PK_USLUGA PRIMARY KEY (KE);
ALTER TABLE USLUGA ADD CONSTRAINT FK_USLU_REFERENCE_DEPA FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT (KE);

CREATE TABLE FACE (
    KE           INTEGER NOT NULL,
    NAI          VARCHAR(120),
    ADDRESS      VARCHAR(120),
    PHONE        VARCHAR(30),
    SEX          INTEGER,
    DOCUM_SERIA  VARCHAR(10),
    DOCUM_NUM    VARCHAR(64),
    GIVEN        VARCHAR(120),
    WHEN_GIVEN   DATE,
    E_MAIL       VARCHAR(50),
    MEMO         VARCHAR(500),
    INN          VARCHAR(12),
    OKPO         VARCHAR(30),
    DELETED      NUMERIC(1,0),
    FACE_TYPE    INTEGER,
    DOCUM        INTEGER,
    BORN         DATE,
    BORNIN       VARCHAR(100)
);
ALTER TABLE FACE ADD CONSTRAINT PK_FACE PRIMARY KEY (KE);
ALTER TABLE FACE ADD CONSTRAINT FK_FACE_1 FOREIGN KEY (DOCUM) REFERENCES B_DOCUMS (KE);

CREATE TABLE IBE$LOG_TABLES (
    ID          NUMERIC(18,0) NOT NULL,
    TABLE_NAME  VARCHAR(67) NOT NULL,
    OPERATION   VARCHAR(1) NOT NULL,
    DATE_TIME   TIMESTAMP NOT NULL,
    USER_NAME   VARCHAR(67) NOT NULL
);
ALTER TABLE IBE$LOG_TABLES ADD PRIMARY KEY (ID);

CREATE TABLE IBE$LOG_BLOB_FIELDS (
    LOG_TABLES_ID   NUMERIC(18,0) NOT NULL,
    FIELD_NAME      VARCHAR(67) NOT NULL,
    OLD_CHAR_VALUE  VARCHAR(500),
    NEW_CHAR_VALUE  VARCHAR(500),
    OLD_BLOB_VALUE  VARCHAR(1000),
    NEW_BLOB_VALUE  VARCHAR(1000)
);
CREATE INDEX IBE$LOG_BLOB_FIELDS_IDX1 ON IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID);

CREATE TABLE IBE$LOG_KEYS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    KEY_FIELD      VARCHAR(67) NOT NULL,
    KEY_VALUE      VARCHAR(255)
);
CREATE INDEX IBE$LOG_KEYS_IDX1 ON IBE$LOG_KEYS (LOG_TABLES_ID);

CREATE TABLE IBE$LOG_FIELDS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    FIELD_NAME     VARCHAR(67) NOT NULL,
    OLD_VALUE      VARCHAR(255),
    NEW_VALUE      VARCHAR(255)
);
CREATE INDEX IBE$LOG_FIELDS_IDX1 ON IBE$LOG_FIELDS (LOG_TABLES_ID);