/*==============*/
/* Versão: 2.16 */
/*==============================================================*/
/* Table: EMPRESAS                                              */
/*==============================================================*/
ALTER TABLE EMPRESAS ADD EMAIL_ENV_NFE VARCHAR(100);
ALTER TABLE EMPRESAS ADD EMAIL_REC_NFE VARCHAR(100);

COMMIT WORK;

/*==============================================================*/
/* Table: CLIENTES                                              */
/*==============================================================*/
ALTER TABLE CLIENTES ADD BAIRRO_ENT VARCHAR(30);

COMMIT WORK;

/*==============================================================*/
/* Table: PAR_NFE                                               */
/*==============================================================*/
ALTER TABLE PAR_NFE ADD FORMA_LOGO     CHAR(3);
ALTER TABLE PAR_NFE ADD CASAS_QTD_PROD SMALLINT;
ALTER TABLE PAR_NFE ADD CASAS_VLR_PROD SMALLINT;
ALTER TABLE PAR_NFE ADD MRG_SUPERIOR   FLOAT;
ALTER TABLE PAR_NFE ADD MRG_INFERIOR   FLOAT;
ALTER TABLE PAR_NFE ADD MRG_ESQUERDA   FLOAT;
ALTER TABLE PAR_NFE ADD MRG_DIREITA    FLOAT;
ALTER TABLE PAR_NFE ADD TIPO_DANFE     CHAR(2);
ALTER TABLE PAR_NFE ADD FORMATO_DANFE  CHAR(1);

COMMIT WORK;

UPDATE PAR_NFE 
SET FORMA_LOGO = 'NOR', 
    CASAS_QTD_PROD = 1, 
    CASAS_VLR_PROD = 2, 
    MRG_SUPERIOR = 0.8, 
    MRG_INFERIOR = 0.8, 
    MRG_ESQUERDA = 0.6,
    MRG_DIREITA = 0.51,
    TIPO_DANFE = 'CB',
    FORMATO_DANFE = 'R';

COMMIT WORK;

/*==============*/
/* Versão: 2.17 */
/*==============================================================*/
/* Table: PAR_NFE                                               */
/*==============================================================*/
alter table PAR_NFE add EMAIL_SERVIDOR VARCHAR(50);
alter table PAR_NFE add EMAIL_PORTA    VARCHAR(10);
alter table PAR_NFE add EMAIL_USER     VARCHAR(75);
alter table PAR_NFE add EMAIL_SENHA    VARCHAR(25);
alter table PAR_NFE add EMAIL_SSL      CHAR(1);
alter table PAR_NFE add EMAIL_TITULO   VARCHAR(100);
alter table PAR_NFE add EMAIL_MESG     VARCHAR(5000);
alter table PAR_NFE add EMAIL_AUTO     CHAR(1);

COMMIT WORK;

/*==============================================================*/
/* Table: EMPRESAS                                              */
/*==============================================================*/
ALTER TABLE EMPRESAS ADD TRIBUTACAO_CRT CHAR(1);

COMMIT WORK;

UPDATE EMPRESAS SET TRIBUTACAO_CRT = '3' WHERE TRIBUTACAO_CRT IS NULL;

COMMIT WORK;

/*==============================================================*/
/* Table: ALIQ_CSOSN                                            */
/*==============================================================*/
create table ALIQ_CSOSN (
COD_CSOSN            SMALLINT                       not null,
DESCRICAO            VARCHAR(45),
ORIG_MERC            CHAR(1),
CSOSN                CHAR(3),
ALIQ_CRED            NUMERIC(9,3),
constraint PK_ALIQ_CSOSN primary key (COD_CSOSN)
);

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
alter table ITENS_NF add COD_CSOSN      SMALLINT;
alter table ITENS_NF add CST_CSOSN      CHAR(3);
alter table ITENS_NF add PERC_CRED_SN   NUMERIC(9,3);
alter table ITENS_NF add VLR_CRED_SN    NUMERIC(15,2);
alter table ITENS_NF add RAT_FRETE      NUMERIC(15,2);
alter table ITENS_NF add RAT_SEGURO     NUMERIC(15,2);
alter table ITENS_NF add RAT_DESPESAS   NUMERIC(15,2);
alter table ITENS_NF add RAT_DESCTO     NUMERIC(15,2);
alter table ITENS_NF add BC_ICMS_FDSDA  NUMERIC(15,2);
alter table ITENS_NF add VLR_ICMS_FDSDA NUMERIC(15,2);

COMMIT WORK;

UPDATE ITENS_NF SET PERC_CRED_SN = 0 WHERE PERC_CRED_SN IS NULL;
UPDATE ITENS_NF SET VLR_CRED_SN = 0 WHERE VLR_CRED_SN IS NULL;
UPDATE ITENS_NF SET RAT_FRETE = 0 WHERE RAT_FRETE IS NULL;
UPDATE ITENS_NF SET RAT_SEGURO = 0 WHERE RAT_SEGURO IS NULL;
UPDATE ITENS_NF SET RAT_DESPESAS = 0 WHERE RAT_DESPESAS IS NULL;
UPDATE ITENS_NF SET RAT_DESCTO = 0 WHERE RAT_DESCTO IS NULL;
UPDATE ITENS_NF SET BC_ICMS_FDSDA = 0 WHERE BC_ICMS_FDSDA IS NULL;
UPDATE ITENS_NF SET VLR_ICMS_FDSDA = 0 WHERE VLR_ICMS_FDSDA IS NULL;

COMMIT WORK;

/*==============================================================*/
/* Table: PRODUTOS                                              */
/*==============================================================*/
alter table PRODUTOS add COD_CSOSN SMALLINT;

COMMIT WORK;

alter table ITENS_NF
   add constraint FK_ITENSNF_ALIQCSOSN foreign key (COD_CSOSN)
      references ALIQ_CSOSN (COD_CSOSN);

alter table PRODUTOS
   add constraint FK_PRODUTOS_ALIQCSOSN foreign key (COD_CSOSN)
      references ALIQ_CSOSN (COD_CSOSN);

COMMIT WORK;

/*==============================================================*/
/* Table: NOTA_FISCAL                                           */
/*==============================================================*/
alter table NOTA_FISCAL add TIPO_PAGTO   CHAR(1);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL INACTIVE;

COMMIT WORK;

UPDATE NOTA_FISCAL NF
SET NF.TIPO_PAGTO = 'P'
WHERE NF.TIPO_PAGTO IS NULL AND
      (SELECT COUNT(PR.SEQ_PARCELAS)
       FROM PARCELAS_NF PR
       WHERE PR.COD_EMPRESA = NF.COD_EMPRESA AND
             PR.TIPO_CONTROL = NF.TIPO_CONTROL AND
             PR.SERIE_NF = NF.SERIE_NF AND
             PR.NRO_NF = NF.NRO_NF) > 0;

COMMIT WORK;

UPDATE NOTA_FISCAL NF
SET NF.TIPO_PAGTO = 'O'
WHERE NF.TIPO_PAGTO IS NULL;

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL ACTIVE;

COMMIT WORK;

/*==============================================================*/
/* Table: PARAMETROS                                            */
/*==============================================================*/
INSERT INTO PARAMETROS
  (COD_PARAMETRO,SEQ_PARAM,NOME_PARAM,DESCR_PARAM,TIPO_PARAM,CLASS_PARAM,VALOR_PARAM)
VALUES
  (1,22,'CHAVE_BUSCACEP_COD_IBGE','Chave para Site www.buscarcep.com.br (Cód. Nacional IBGE)','STRING','2','');

COMMIT WORK;

/*==============================================================*/
/* Trigger: TG_UPDATE_NOTAFISCAL                                */
/*==============================================================*/

SET TERM ^ ;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL
ACTIVE BEFORE UPDATE POSITION 0
AS
    DECLARE VARIABLE varCOD_EMPRESA INTEGER;
    DECLARE VARIABLE varTIPO_CONTROL CHAR(1);
    DECLARE VARIABLE varSERIE_NF INTEGER;
    DECLARE VARIABLE varNRO_NF INTEGER;
    DECLARE VARIABLE varVLR_ITENS DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_IPI DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS2 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS2 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CONFERE DECIMAL(15,2);
    DECLARE VARIABLE varSEQ_PRIM_ITEM INTEGER;
    DECLARE VARIABLE varSEQ_ITEM INTEGER;
    DECLARE VARIABLE varVLR_OUTROS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_DIF DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CRED_SN DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ITEM DECIMAL(15,2);
    DECLARE VARIABLE varVLR_RATITEM DECIMAL(15,2);
    DECLARE VARIABLE varPERC_CRED_SN DECIMAL(9,3);
BEGIN
     /* SELEÇÃO DOS VALORES DOS ITENS DE PRODUTOS */
     SELECT INF.NRO_NF,
            SUM(INF.VLR_TOTAL),
            SUM(INF.BASE_ICMS),
            SUM(INF.VLR_ICMS),
            SUM(INF.VLR_IPI)
     FROM ITENS_NF INF
     WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
           INF.SERIE_NF = NEW.SERIE_NF AND
           INF.NRO_NF = NEW.NRO_NF AND
           INF.TIPO_CONTROL = NEW.TIPO_CONTROL
     GROUP BY INF.NRO_NF
     INTO :varNRO_NF, :varVLR_ITENS, :varBASE_ICMS, :varVLR_ICMS, :varVLR_IPI;

     NEW.VLR_TOTAL = NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC;

     IF ((varVLR_ITENS IS NOT NULL) AND (varVLR_ITENS > 0)) THEN
     BEGIN
          UPDATE ITENS_NF INF
          SET INF.VLR_RATEIO = (INF.VLR_TOTAL / :varVLR_ITENS) *
             (NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC)
          WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                INF.SERIE_NF = NEW.SERIE_NF AND
                INF.NRO_NF = NEW.NRO_NF AND
                INF.TIPO_CONTROL = NEW.TIPO_CONTROL;

          NEW.VLR_PROD = :varVLR_ITENS;
          NEW.BASE_ICMS = :varBASE_ICMS;
          NEW.VLR_ICMS = :varVLR_ICMS;
          NEW.VLR_IPI = :varVLR_IPI;
          NEW.VLR_TOTAL = :varVLR_ITENS + NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC;

          IF (NEW.VLR_FRETE > 0) THEN
          BEGIN
               UPDATE ITENS_NF INF
               SET INF.RAT_FRETE = (INF.VLR_TOTAL * NEW.VLR_FRETE) / :varVLR_ITENS
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL;

               SELECT MIN(INF.SEQUENCIA),
                      SUM(INF.RAT_FRETE)
               FROM ITENS_NF INF
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL
               INTO :varSEQ_PRIM_ITEM, :varVLR_CONFERE;

               IF (NEW.VLR_FRETE <> varVLR_CONFERE) THEN
               BEGIN
                    varVLR_DIF = NEW.VLR_FRETE - varVLR_CONFERE;

                    UPDATE ITENS_NF  INF
                    SET INF.RAT_FRETE = INF.RAT_FRETE + :varVLR_DIF
                    WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                          INF.SERIE_NF = NEW.SERIE_NF AND
                          INF.NRO_NF = NEW.NRO_NF AND
                          INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                          INF.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

          IF (NEW.VLR_SEGURO > 0) THEN
          BEGIN
               UPDATE ITENS_NF  INF
               SET INF.RAT_SEGURO = (INF.VLR_TOTAL * NEW.VLR_SEGURO) / :varVLR_ITENS
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL;

               SELECT MIN(INF.SEQUENCIA),
                      SUM(INF.RAT_SEGURO)
               FROM ITENS_NF INF
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL
               INTO :varSEQ_PRIM_ITEM, :varVLR_CONFERE;

               IF (NEW.VLR_SEGURO <> varVLR_CONFERE) THEN
               BEGIN
                    varVLR_DIF = NEW.VLR_SEGURO - varVLR_CONFERE;

                    UPDATE ITENS_NF  INF
                    SET INF.RAT_SEGURO = INF.RAT_SEGURO + :varVLR_DIF
                    WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                          INF.SERIE_NF = NEW.SERIE_NF AND
                          INF.NRO_NF = NEW.NRO_NF AND
                          INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                          INF.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

          varVLR_OUTROS = NEW.VLR_OUTDESP + NEW.VLR_ACRES;
          IF (varVLR_OUTROS > 0) THEN
          BEGIN
               UPDATE ITENS_NF  INF
               SET INF.RAT_DESPESAS = (INF.VLR_TOTAL * :varVLR_OUTROS) / :varVLR_ITENS
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL;

               SELECT MIN(INF.SEQUENCIA),
                      SUM(INF.RAT_DESPESAS)
               FROM ITENS_NF INF
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL
               INTO :varSEQ_PRIM_ITEM, :varVLR_CONFERE;

               IF (varVLR_OUTROS <> varVLR_CONFERE) THEN
               BEGIN
                    varVLR_DIF = varVLR_OUTROS - varVLR_CONFERE;

                    UPDATE ITENS_NF  INF
                    SET INF.RAT_DESPESAS = INF.RAT_DESPESAS + :varVLR_DIF
                    WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                          INF.SERIE_NF = NEW.SERIE_NF AND
                          INF.NRO_NF = NEW.NRO_NF AND
                          INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                          INF.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

          IF (NEW.VLR_DESC > 0) THEN
          BEGIN
               UPDATE ITENS_NF  INF
               SET INF.RAT_DESCTO = (INF.VLR_TOTAL * NEW.VLR_DESC) / :varVLR_ITENS
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL;

               SELECT MIN(INF.SEQUENCIA),
                      SUM(INF.RAT_DESCTO)
               FROM ITENS_NF INF
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL
               INTO :varSEQ_PRIM_ITEM, :varVLR_CONFERE;

               IF (NEW.VLR_DESC <> varVLR_CONFERE) THEN
               BEGIN
                    varVLR_DIF = NEW.VLR_DESC - varVLR_CONFERE;

                    UPDATE ITENS_NF  INF
                    SET INF.RAT_DESCTO = INF.RAT_DESCTO + :varVLR_DIF
                    WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                          INF.SERIE_NF = NEW.SERIE_NF AND
                          INF.NRO_NF = NEW.NRO_NF AND
                          INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                          INF.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

          /* varSEQ_ITEM, varVLR_CRED_SN, varVLR_ITEM, varVLR_RATITEM, varPERC_CRED_SN */
          /* CALCULO DO VALOR DE CRÉDITO PARA SIMPLES NACIONAL */
          FOR
               SELECT INF.SEQUENCIA,
                      INF.VLR_TOTAL,
                      INF.VLR_RATEIO,
                      INF.PERC_CRED_SN
               FROM ITENS_NF INF
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                     INF.PERC_CRED_SN > 0
               INTO :varSEQ_ITEM, :varVLR_ITEM, :varVLR_RATITEM, :varPERC_CRED_SN
          DO
          BEGIN
               IF (varVLR_RATITEM IS NULL) THEN
                    varVLR_RATITEM = 0;

               varVLR_CRED_SN = ((varVLR_ITEM + varVLR_RATITEM) * varPERC_CRED_SN) / 100;

               UPDATE ITENS_NF  INF
               SET INF.VLR_CRED_SN = :varVLR_CRED_SN
               WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                     INF.SERIE_NF = NEW.SERIE_NF AND
                     INF.NRO_NF = NEW.NRO_NF AND
                     INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                     INF.SEQUENCIA = :varSEQ_ITEM;
          END
     END

     SELECT ANF.NRO_NF,
            SUM(ANF.BASE_ICMS),
            SUM(ANF.VLR_ICMS)
     FROM ALIQ_ICMSNF ANF
     WHERE ANF.COD_EMPRESA = NEW.COD_EMPRESA AND
           ANF.SERIE_NF = NEW.SERIE_NF AND
           ANF.NRO_NF = NEW.NRO_NF AND
           ANF.TIPO_CONTROL = NEW.TIPO_CONTROL
     GROUP BY ANF.NRO_NF
     INTO :varNRO_NF, :varBASE_ICMS2, :varVLR_ICMS2;

     IF (:varBASE_ICMS2 IS NOT NULL) THEN
     BEGIN
           NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS2;
           NEW.VLR_ICMS  = NEW.VLR_ICMS  + :varVLR_ICMS2;
     END
END^

SET TERM ; ^

COMMIT WORK;

/*==============================================================*/
/* Table: CLASS_FISCAL                                          */
/*==============================================================*/
ALTER TABLE CLASS_FISCAL ALTER COLUMN DESCRICAO_NTB TO DESCRICAO;

ALTER TABLE CLASS_FISCAL
    ALTER COLUMN DESCRICAO TYPE VARCHAR(80) CHARACTER SET ISO8859_1;

COMMIT WORK;

/*==============================================================*/
/* Table: ALIQ_UF_SUBST                                         */
/*==============================================================*/
create table ALIQ_UF_SUBST (
COD_ICMS             SMALLINT                       not null,
UF                   CHAR(2)                        not null,
PERC_SUBST           NUMERIC(9,4),
PERC_PROPRIO         NUMERIC(9,4),
constraint PK_ALIQ_UF_SUBST primary key (COD_ICMS, UF)
);

/*==============================================================*/
/* Table: CLASS_UF_MVA                                          */
/*==============================================================*/
create table CLASS_UF_MVA (
COD_CLASS            VARCHAR(20)                    not null,
UF                   CHAR(2)                        not null,
PERC_MVA             NUMERIC(9,4),
constraint PK_CLASS_UF_MVA primary key (COD_CLASS, UF)
);

alter table ALIQ_UF_SUBST
   add constraint FK_ALIQ_UF_SUBST_ICMS foreign key (COD_ICMS)
      references ALIQ_ICMS (COD_ICMS);

alter table CLASS_UF_MVA
   add constraint FK_CLASS_UF_MVA_FISCAL foreign key (COD_CLASS)
      references CLASS_FISCAL (COD_CLASS);

COMMIT WORK;

/*==============================================================*/
/* Table: OPER_FISCAL                                           */
/*==============================================================*/
ALTER TABLE OPER_FISCAL ADD TIPO_OPER CHAR(2);
ALTER TABLE OPER_FISCAL ADD DEST_OPER CHAR(1);
ALTER TABLE OPER_FISCAL ADD CFOP      CHAR(6);

COMMIT WORK;

UPDATE OPER_FISCAL SET CFOP = COD_OPEFISC WHERE CFOP IS NULL;

COMMIT WORK;

/*==============================================================*/
/* Table: NOTA_FISCAL                                           */
/*==============================================================*/
ALTER TABLE NOTA_FISCAL DROP CONSTRAINT FK_NOTA_FISCAL_OPER_FISCAL;

ALTER TABLE NOTA_FISCAL ALTER COLUMN COD_OPEFISC TO CFOP;

COMMIT WORK;

ALTER TABLE NOTA_FISCAL ADD COD_OPEFISC SMALLINT;

COMMIT WORK;

/*==============================================================*/
/* Table: ENTRADAS                                              */
/*==============================================================*/
ALTER TABLE ENTRADAS DROP CONSTRAINT FK_ENTRADAS_OPER_FISCAL;

ALTER TABLE ENTRADAS ALTER COLUMN COD_OPEFISC TO CFOP;

COMMIT WORK;

ALTER TABLE ENTRADAS ADD COD_OPEFISC SMALLINT;

COMMIT WORK;

/*==============================================================*/
/* Table: OPER_FISCAL                                           */
/*==============================================================*/
ALTER TABLE OPER_FISCAL DROP CONSTRAINT PK_OPER_FISCAL;

COMMIT WORK;

alter table OPER_FISCAL drop COD_OPEFISC;

COMMIT WORK;

ALTER TABLE OPER_FISCAL ADD COD_OPEFISC SMALLINT;

COMMIT WORK;

CREATE GENERATOR GEN_OPER_FISCAL;

COMMIT WORK;

UPDATE OPER_FISCAL SET COD_OPEFISC = GEN_ID(GEN_OPER_FISCAL, 1);

COMMIT WORK;

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'COD_OPEFISC') and (RDB$RELATION_NAME = 'OPER_FISCAL');

COMMIT WORK;

alter table OPER_FISCAL
    add constraint PK_OPER_FISCAL primary key (COD_OPEFISC);

COMMIT WORK;

/*==============================================================*/
/* Table: NOTA_FISCAL                                           */
/*==============================================================*/
ALTER TABLE NOTA_FISCAL ADD DEST_OPER CHAR(1);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL INACTIVE;

COMMIT WORK;

UPDATE NOTA_FISCAL NF
SET NF.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = NF.CFOP);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL ACTIVE;

COMMIT WORK;

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'COD_OPEFISC') and (RDB$RELATION_NAME = 'NOTA_FISCAL');

COMMIT WORK;

alter table NOTA_FISCAL
    add constraint FK_NOTA_FISCAL_OPER_FISCAL
        foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);

COMMIT WORK;

/*==============================================================*/
/* Table: ENTRADAS                                              */
/*==============================================================*/
ALTER TRIGGER TG_UPDATE_ENTRADAS INACTIVE;

COMMIT WORK;

UPDATE ENTRADAS ET
SET ET.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = ET.CFOP);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_ENTRADAS ACTIVE;

COMMIT WORK;

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'COD_OPEFISC') and (RDB$RELATION_NAME = 'ENTRADAS');

COMMIT WORK;

alter table ENTRADAS
    add constraint FK_ENTRADAS_OPER_FISCAL
        foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);

COMMIT WORK;

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
alter table ITENS_NF add PERC_MVA      NUMERIC(9,4);
alter table ITENS_NF add PERC_PROPRIO  NUMERIC(9,4);
alter table ITENS_NF add PERC_SUBST    NUMERIC(9,4);
alter table ITENS_NF add BASE_SUBST    NUMERIC(15,2);
alter table ITENS_NF add VLR_SUBST     NUMERIC(15,2);

COMMIT WORK;

UPDATE ITENS_NF SET PERC_MVA = 0 WHERE PERC_MVA IS NULL;
UPDATE ITENS_NF SET PERC_PROPRIO = 0 WHERE PERC_PROPRIO IS NULL;
UPDATE ITENS_NF SET PERC_SUBST = 0 WHERE PERC_SUBST IS NULL;
UPDATE ITENS_NF SET BASE_SUBST = 0 WHERE BASE_SUBST IS NULL;
UPDATE ITENS_NF SET VLR_SUBST = 0 WHERE VLR_SUBST IS NULL;

COMMIT WORK;

/*==============*/
/* Versão: 2.22 */
/*==============================================================*/
/* Table: NOTA_FISCAL                                           */
/*==============================================================*/
ALTER TABLE NOTA_FISCAL ADD FINALIDADE_NF CHAR(1);

COMMIT WORK;

UPDATE NOTA_FISCAL SET FINALIDADE_NF = '1' WHERE FINALIDADE_NF IS NULL;

COMMIT WORK;

/*==============================================================*/
/* Table: NOTA_FISCAL_REF                                       */
/*==============================================================*/
create table NOTA_FISCAL_REF (
COD_EMPRESA          SMALLINT                       not null,
TIPO_CONTROL         CHAR(1)                        not null,
SERIE_NF             SMALLINT                       not null,
NRO_NF               INTEGER                        not null,
TIPO_REF             VARCHAR(10),
NRO_DOCTO            VARCHAR(15),
MODELO               CHAR(2),
SERIE_SEQ            CHAR(3),
MESANO               CHAR(4),
CPF                  VARCHAR(15),
CNPJ                 VARCHAR(20),
UF                   CHAR(2),
INSCR_EST            VARCHAR(15),
CHAVE_NFE            VARCHAR(50),
constraint PK_NOTA_FISCAL_REF primary key (COD_EMPRESA, TIPO_CONTROL, SERIE_NF, NRO_NF)
);

COMMIT WORK;

alter table NOTA_FISCAL_REF
   add constraint FK_NFREF_NOTAFISCAL foreign key (COD_EMPRESA, TIPO_CONTROL, SERIE_NF, NRO_NF)
      references NOTA_FISCAL (COD_EMPRESA, TIPO_CONTROL, SERIE_NF, NRO_NF);

COMMIT WORK;