
/*------ ATD 376123 ------*/
DECLARE v_count NUMBER:=0;
BEGIN
   SELECT count(1) INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'ESTACAO' ;
   IF v_count=0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE ESTACAO (
				ID        INT          NOT NULL,
				NOME      VARCHAR(254),           
				PERFIL    NUMERIC (1,0)  DEFAULT 0, 
				IDPERFIL   INT DEFAULT 0)';
        EXECUTE IMMEDIATE 'alter table ESTACAO ADD CONSTRAINT PK_ESTACAO PRIMARY KEY (ID)'; 
        EXECUTE IMMEDIATE 'CREATE SEQUENCE ESTACAO_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ESTACAO
                BEFORE INSERT ON ESTACAO for each ROW
                WHEN (new.ID IS NULL)
                  BEGIN
                   SELECT ESTACAO_SEQ.NEXTVAL INTO :new.ID FROM dual;
                END;';
    END IF ;
END;




DECLARE v_count NUMBER:=0;
BEGIN
   SELECT count(1) INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'GRUPOCONFIGURACAOESTACAO' ;
   IF v_count=0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE GRUPOCONFIGURACAOESTACAO (
				ID        INT          NOT NULL,
				NOME      VARCHAR(254))';           
        EXECUTE IMMEDIATE 'alter table GRUPOCONFIGURACAOESTACAO ADD CONSTRAINT PK_GRUPOCONFIGURACAOESTACAO PRIMARY KEY (ID)'; 
        EXECUTE IMMEDIATE 'CREATE SEQUENCE GRUPOCONFIGURACAOESTACAO_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER GRUPOCONFIGURACAOESTACAO
                BEFORE INSERT ON GRUPOCONFIGURACAOESTACAO for each ROW
                WHEN (new.ID IS NULL)
                  BEGIN
                   SELECT GRUPOCONFIGURACAOESTACAO_SEQ.NEXTVAL INTO :new.ID FROM dual;
                END;';
    END IF ;
END;


DECLARE v_count NUMBER:=0;
BEGIN
   SELECT count(1) INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'CONFIGURACAOESTACAO' ;
   IF v_count=0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE CONFIGURACAOESTACAO (
				ID         INT NOT NULL,
				IDESTACAO  INT,
				IDGRUPO    INT, 
				NOME       VARCHAR(254),
				VALOR      VARCHAR(254),
				TIPO       VARCHAR(50),
				PREVALECE  NUMERIC(1,0) DEFAULT 0)';
				
        EXECUTE IMMEDIATE 'alter table CONFIGURACAOESTACAO ADD CONSTRAINT PK_CONFIGURACAOESTACAO PRIMARY KEY (ID)'; 
        EXECUTE IMMEDIATE 'CREATE SEQUENCE GRUPOCONFIGURACAOESTACAO_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER CONFIGURACAOESTACAO
                BEFORE INSERT ON CONFIGURACAOESTACAO for each ROW
                WHEN (new.ID IS NULL)
                  BEGIN
                   SELECT CONFIGURACAOESTACAO_SEQ.NEXTVAL INTO :new.ID FROM dual;
                END;';
    END IF ;
END;

MERGE into GRUPOCONFIGURACAOESTACAO using dual on (GRUPOCONFIGURACAOESTACAO.NOME ='NFCE' ) when not matched then
insert (nome) values ('NFCE');
