CREATE DOMAIN MONEYRUB_NUM AS
NUMERIC(18,6);

/******************************************************************************/
/***               Generated by IBExpert 06.04.2015 15:37:13                ***/
/******************************************************************************/

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



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE DOCTAB_AMOUNTBALANCE_NOM (
    ID            KEYS NOT NULL /* KEYS = CHAR(16) */,
    OWNER         KEYS /* KEYS = CHAR(16) */,
    NOMENCLATURE  KEYS /* KEYS = CHAR(16) */,
    UNIT          KEYS /* KEYS = CHAR(16) */,
    FINSOURCE     KEYS /* KEYS = CHAR(16) */,
    SERIES        KEYS /* KEYS = CHAR(16) */,
    PARTIES       KEYS /* KEYS = CHAR(16) */,
    SUMMA         MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    PRICENOM      MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    PRICENOM1     MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    SUMMA1        MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT PK_DOCTAB_AMOUNTBALANCE_NOM PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_1 FOREIGN KEY (OWNER) REFERENCES DOC_AMOUNTBALANCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_2 FOREIGN KEY (NOMENCLATURE) REFERENCES REF_NOMENCLATURE (ID) ON UPDATE CASCADE;
ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_3 FOREIGN KEY (UNIT) REFERENCES REF_UNITS (ID) ON UPDATE CASCADE;
ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_4 FOREIGN KEY (FINSOURCE) REFERENCES REF_FINSOURCES (ID) ON UPDATE CASCADE;
ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_5 FOREIGN KEY (SERIES) REFERENCES REF_SERIES (ID) ON UPDATE CASCADE;
ALTER TABLE DOCTAB_AMOUNTBALANCE_NOM ADD CONSTRAINT FK_DOCTAB_AMOUNTBALANCE_NOM_6 FOREIGN KEY (PARTIES) REFERENCES REF_PARTIES (ID) ON UPDATE CASCADE;


/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

COMMENT ON TABLE DOCTAB_AMOUNTBALANCE_NOM IS 
'Документ "Корректировка суммы остатка" (Табличная часть "Номенклатура")';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.ID IS 
'Идентификатор';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.OWNER IS 
'Документ-владелец';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.NOMENCLATURE IS 
'Номенклатура';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.UNIT IS 
'Упаковка';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.FINSOURCE IS 
'Единица измерения';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.SERIES IS 
'Количество';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.PARTIES IS 
'Коэффициент';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.SUMMA IS 
'Сумма';

COMMENT ON COLUMN DOCTAB_AMOUNTBALANCE_NOM.PRICENOM IS 
'Цена';

/******************************************************************************/
/***               Generated by IBExpert 06.04.2015 15:42:55                ***/
/******************************************************************************/

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



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE DOCTAB_OPER_USLUGI (
    OWNER             KEYS NOT NULL /* KEYS = CHAR(16) */,
    USLUGA            KEYS /* KEYS = CHAR(16) */,
    MEDPERSONAL       KEYS /* KEYS = CHAR(16) */,
    DOLGNOST          KEYS /* KEYS = CHAR(16) */,
    PROFMEDHELP       KEYS /* KEYS = CHAR(16) */,
    RESULT_OBR        KEYS /* KEYS = CHAR(16) */,
    KOL               MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    KOLPLACE          MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    ITOG              MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    DATEON            DOMEN_DATE /* DOMEN_DATE = DATE */,
    ID                KEYS NOT NULL /* KEYS = CHAR(16) */,
    OPLATA_TYPE       KEYS /* KEYS = CHAR(16) */,
    OWNER_DOC         KEYS /* KEYS = CHAR(16) */,
    SUMMA             MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    SUMMAMED          MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    IS_THIS_ANALYS    BOOLEAN /* BOOLEAN = SMALLINT */,
    DS                KEYS /* KEYS = CHAR(16) */,
    CODEST            STRING40 /* STRING40 = VARCHAR(40) */,
    IS_NOT_RASCHET    BOOLEAN /* BOOLEAN = SMALLINT */,
    SUM_ONE           MONEYRUB /* MONEYRUB = DOUBLE PRECISION */,
    DATE_ADD          DOMEN_DATE /* DOMEN_DATE = DATE */,
    N_TOOTH           "INT" /* "INT" = INTEGER */,
    IS_MILK_TOOTH     BOOLEAN /* BOOLEAN = SMALLINT */,
    AUTHOR            KEYS /* KEYS = CHAR(16) */,
    DATEON_END        DOMEN_DATE /* DOMEN_DATE = DATE */,
    IS_OBR_USLUGA     BOOLEAN /* BOOLEAN = SMALLINT */,
    IS_DEFECT_PLOMB   BOOLEAN /* BOOLEAN = SMALLINT */,
    RESULT            STRING255 /* STRING255 = VARCHAR(255) */,
    BRANCH            KEYS /* KEYS = CHAR(16) */,
    IS_NOT_EXPORT     BOOLEAN /* BOOLEAN = SMALLINT */,
    MEDPERSONAL_SRED  KEYS /* KEYS = CHAR(16) */,
    HOSPITAL_TYPE     KEYS /* KEYS = CHAR(16) */,
    KOL1              MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    KOLPLACE1         MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    ITOG1             MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    SUMMA1            MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    SUMMAMED1         MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */,
    SUM_ONE1          MONEYRUB_NUM /* MONEYRUB_NUM = NUMERIC(18,6) */
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT PK_DOCTAB_OPER_USLUGI PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_1 FOREIGN KEY (USLUGA) REFERENCES REF_USLUGI (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_10 FOREIGN KEY (MEDPERSONAL_SRED) REFERENCES REF_MEDPERSONAL (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_11 FOREIGN KEY (HOSPITAL_TYPE) REFERENCES REF_HOSPITAL_TYPE (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_2 FOREIGN KEY (MEDPERSONAL) REFERENCES REF_MEDPERSONAL (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_3 FOREIGN KEY (DOLGNOST) REFERENCES REF_DOLGNOSTI (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_4 FOREIGN KEY (PROFMEDHELP) REFERENCES REF_PROFMEDHELP (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_5 FOREIGN KEY (OWNER) REFERENCES DOC_RECEPTION (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_6 FOREIGN KEY (OPLATA_TYPE) REFERENCES REF_OPLATA_TYPE (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_7 FOREIGN KEY (RESULT_OBR) REFERENCES REF_RESULT_OBR (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_8 FOREIGN KEY (DS) REFERENCES REF_MKB (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE DOCTAB_OPER_USLUGI ADD CONSTRAINT FK_DOCTAB_OPER_USLUGI_9 FOREIGN KEY (AUTHOR) REFERENCES REF_USERS (ID) ON DELETE SET NULL ON UPDATE CASCADE;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX DOCTAB_OPER_USLUGI_IDX1 ON DOCTAB_OPER_USLUGI (OWNER_DOC);
CREATE INDEX DOCTAB_OPER_USLUGI_IDX2 ON DOCTAB_OPER_USLUGI (DATEON);
CREATE INDEX DOCTAB_OPER_USLUGI_IDX3 ON DOCTAB_OPER_USLUGI COMPUTED BY ((coalesce(IS_THIS_ANALYS, 0)));
CREATE INDEX DOCTAB_OPER_USLUGI_IDX4 ON DOCTAB_OPER_USLUGI (OWNER, OPLATA_TYPE);
CREATE INDEX DOCTAB_OPER_USLUGI_IDX5 ON DOCTAB_OPER_USLUGI (OWNER, DATEON, USLUGA);
CREATE INDEX DOCTAB_OPER_USLUGI_IDXIS_OBR ON DOCTAB_OPER_USLUGI COMPUTED BY ((coalesce(IS_OBR_USLUGA, 0)));


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: DOCTAB_OPER_USLUGI_AD0 */
CREATE OR ALTER TRIGGER DOCTAB_OPER_USLUGI_AD0 FOR DOCTAB_OPER_USLUGI
ACTIVE AFTER DELETE POSITION 0
AS
begin
 delete from reg_oper_tarif a where a.owner=old.id;
end
^


/* Trigger: DOCTAB_OPER_USLUGI_BI0 */
CREATE OR ALTER TRIGGER DOCTAB_OPER_USLUGI_BI0 FOR DOCTAB_OPER_USLUGI
ACTIVE BEFORE INSERT POSITION 0
AS
begin
 new.date_add=current_date;
end
^


SET TERM ; ^



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

COMMENT ON TABLE DOCTAB_OPER_USLUGI IS 
'Документ "Свод услуг" (Табличная часть "Оказанные услуги")';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.OWNER IS 
'Документ-владелец';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.USLUGA IS 
'Услуга';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.MEDPERSONAL IS 
'Мед. персонал, оказавший услугу';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.DOLGNOST IS 
'Должность мед. персонала, оказывавшего услугу';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.PROFMEDHELP IS 
'Профиль мед. помощи';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.RESULT_OBR IS 
'Результат обращения';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.KOL IS 
'Кол-во услуги';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.KOLPLACE IS 
'Кол-во мест применения';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.ITOG IS 
'Итого';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.DATEON IS 
'Дата оказания услуги';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.ID IS 
'Идентификатор записи';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.OPLATA_TYPE IS 
'Вид оплаты';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.OWNER_DOC IS 
'Источник данных';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.SUMMA IS 
'Сумма по строке';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.SUMMAMED IS 
'Сумма медикаментов';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_THIS_ANALYS IS 
'Это исследование';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.DS IS 
'Диагноз';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.CODEST IS 
'Код услуги, введеный операторами';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_NOT_RASCHET IS 
'Не расчитывать услугу';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.DATE_ADD IS 
'Дата добавления услуги';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.N_TOOTH IS 
'Номер зуба';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_MILK_TOOTH IS 
'Признак того, что зуб является молочным';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.AUTHOR IS 
'Автор записи в табличной части';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.DATEON_END IS 
'Дата окончания услуги';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_OBR_USLUGA IS 
'Является услугой законченного случая';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_DEFECT_PLOMB IS 
'Дефект пломбы';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.RESULT IS 
'Результат обследования';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.BRANCH IS 
'Филиал (подразделение МО)';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.IS_NOT_EXPORT IS 
'Не выгружать, справочно';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.MEDPERSONAL_SRED IS 
'Соисполнитель. Мед. сестра';

COMMENT ON COLUMN DOCTAB_OPER_USLUGI.HOSPITAL_TYPE IS 
'“ип госпитализации';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


/* Privileges of users */
GRANT ALL ON DOCTAB_OPER_USLUGI TO KSAMUUSER;