/*================*/
/* Versão: 2.5.26 */
/*==============================================================*/
/* Table: CLASS_TRIB                                            */
/*==============================================================*/
create table CLASS_TRIB (
COD_TRIB             VARCHAR(5)                     not null,
DESCRICAO            VARCHAR(60),
constraint PK_CLASS_TRIB primary key (COD_TRIB)
);

COMMIT WORK;

INSERT INTO CLASS_TRIB (COD_TRIB,DESCRICAO) VALUES ('00','CLASSIFICAÇÃO NORMAL');
INSERT INTO CLASS_TRIB (COD_TRIB,DESCRICAO) VALUES ('01','ALÍQUOTAS REDUZIDAS A ZERO PARA PIS E COFINS');
INSERT INTO CLASS_TRIB (COD_TRIB,DESCRICAO) VALUES ('02','ALÍQUOTA ZERO - TRIBUTAÇÃO MONOFÁSICA PIS / COFINS');
INSERT INTO CLASS_TRIB (COD_TRIB,DESCRICAO) VALUES ('03','SUBSTITUIÇÃO TRIBUTÁRIA PIS E COFINS');

COMMIT WORK;

/*==============================================================*/
/* Table: PRODUTOS                                              */
/*==============================================================*/
ALTER TABLE PRODUTOS ADD COD_TRIB VARCHAR(5);

COMMIT WORK;

alter table PRODUTOS
   add constraint FK_PRODUTOS_CLASS_TRIB
      foreign key (COD_TRIB) references CLASS_TRIB(COD_TRIB);

COMMIT WORK;

UPDATE PRODUTOS SET COD_TRIB = COD_FISCAL
WHERE (COD_FISCAL = '00' OR COD_FISCAL = '01' OR
       COD_FISCAL = '02' OR COD_FISCAL = '03') AND
      COD_TRIB IS NULL;

COMMIT WORK;

UPDATE PRODUTOS SET COD_FISCAL = NULL
WHERE (COD_FISCAL = '00' OR COD_FISCAL = '01' OR
       COD_FISCAL = '02' OR COD_FISCAL = '03');

COMMIT WORK;

/*==============================================================*/
/* Table: CLASS_FISCAL                                          */
/*==============================================================*/
DELETE FROM CLASS_FISCAL WHERE COD_FISCAL = '00';
DELETE FROM CLASS_FISCAL WHERE COD_FISCAL = '01';
DELETE FROM CLASS_FISCAL WHERE COD_FISCAL = '02';
DELETE FROM CLASS_FISCAL WHERE COD_FISCAL = '03';

COMMIT WORK;

/*==============================================================*/
/* Table: NOTAFISCAL                                            */
/*==============================================================*/
ALTER TABLE NOTAFISCAL ADD OPCAO_NF   CHAR(2);
ALTER TABLE NOTAFISCAL ADD REF_NRONF  VARCHAR(50);
ALTER TABLE NOTAFISCAL ADD REF_TIPONF CHAR(3);
ALTER TABLE NOTAFISCAL ADD REF_AAMM   CHAR(4);
ALTER TABLE NOTAFISCAL ADD REF_MODELO CHAR(2);
ALTER TABLE NOTAFISCAL ADD REF_SERIE  VARCHAR(5);

ALTER TABLE NOTAFISCAL ADD DESCR_CFOP  VARCHAR(25);
ALTER TABLE NOTAFISCAL ALTER COLUMN DESC_OPERACAO TO DESCR_OPERACAO;

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL INACTIVE;

COMMIT WORK;

UPDATE NOTAFISCAL SET OPCAO_NF = 'NO' WHERE OPCAO_NF IS NULL;

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL ACTIVE;

COMMIT WORK;

/*==============================================================*/
/* Table: CLASS_FISCAL                                          */
/*==============================================================*/
ALTER TABLE CLASS_FISCAL ALTER COLUMN PERC_BASE TO PERC_MVA_INT;

ALTER TABLE CLASS_FISCAL ADD PERC_MVA_EXT NUMERIC(9,4);

COMMIT WORK;

UPDATE CLASS_FISCAL SET PERC_MVA_INT = 0 WHERE PERC_MVA_INT IS NULL;
UPDATE CLASS_FISCAL SET PERC_MVA_EXT = 0 WHERE PERC_MVA_EXT IS NULL;

COMMIT WORK;

/*================*/
/* Versão: 2.5.27 */
/*==============================================================*/
/* Table: ENTRADAS                                              */
/*==============================================================*/
update RDB$RELATION_FIELDS set RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'CFOP') and (RDB$RELATION_NAME = 'ENTRADAS');

COMMIT WORK;

/*================*/
/* Versão: 2.5.30 */
/*==============================================================*/
/* 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_ITEM DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS3 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS3 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_SERV DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CONFERE DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CONFERE2 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_DIF DECIMAL(15,2);
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_ITEM, :varBASE_ICMS, :varVLR_ICMS, :varVLR_IPI;

        /* SELEÇÃO DOS VALORES DOS ITENS DE SERVICO */
        SELECT SVF.NRO_NF,
               SUM(SVF.VLR_TOTAL),
               SUM(SVF.BASE_ICMS),
               SUM(SVF.VLR_ICMS)
        FROM SERVICOS_NF SVF
        WHERE SVF.COD_EMPRESA = NEW.COD_EMPRESA AND
              SVF.SERIE_NF = NEW.SERIE_NF AND
              SVF.NRO_NF = NEW.NRO_NF AND
              SVF.TIPO_CONTROL = NEW.TIPO_CONTROL
        GROUP BY SVF.NRO_NF
        INTO :varNRO_NF, :varVLR_SERV, :varBASE_ICMS3, :varVLR_ICMS3;

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

        IF (varVLR_SERV IS NULL) THEN
            varVLR_SERV = 0;
        IF (varBASE_ICMS3 IS NULL) THEN
            varBASE_ICMS3 = 0;
        IF (varVLR_ICMS3 IS NULL) THEN
            varVLR_ICMS3 = 0;

        IF (varVLR_ITEM IS NULL) THEN
            varVLR_ITEM = 0;
        IF (varBASE_ICMS IS NULL) THEN
            varBASE_ICMS = 0;
        IF (varVLR_ICMS IS NULL) THEN
            varVLR_ICMS = 0;
        IF (varVLR_IPI IS NULL) THEN
            varVLR_IPI = 0;

        NEW.VLR_PROD = :varVLR_ITEM;
        NEW.VLR_SERV = :varVLR_SERV;
        NEW.BASE_ICMS = :varBASE_ICMS + :varBASE_ICMS3;
        NEW.VLR_ICMS = :varVLR_ICMS + :varVLR_ICMS3;
        NEW.VLR_IPI = :varVLR_IPI;
        NEW.VLR_TOTAL = :varVLR_ITEM + :varVLR_SERV + NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC;

        IF (varVLR_ITEM > 0) THEN
        BEGIN
            UPDATE ITENS_NF  INF
            SET INF.VLR_RATEIO = (INF.VLR_TOTAL / :varVLR_ITEM) *
               (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;

            IF (NEW.VLR_FRETE > 0) THEN
            BEGIN
                 UPDATE ITENS_NF  INF
                 SET INF.RAT_FRETE = (INF.VLR_TOTAL * NEW.VLR_FRETE) / :varVLR_ITEM
                 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 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 :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 = 1;
                 END
            END
            
            IF (NEW.VLR_SEGURO > 0) THEN
            BEGIN
                 UPDATE ITENS_NF  INF
                 SET INF.RAT_SEGURO = (INF.VLR_TOTAL * NEW.VLR_SEGURO) / :varVLR_ITEM
                 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 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 :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 = 1;
                 END
            END
            
            IF (NEW.VLR_OUTDESP > 0) THEN
            BEGIN
                 UPDATE ITENS_NF  INF
                 SET INF.RAT_DESPESAS = (INF.VLR_TOTAL * NEW.VLR_OUTDESP) / :varVLR_ITEM
                 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 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 :varVLR_CONFERE;

                 IF (NEW.VLR_OUTDESP <> varVLR_CONFERE) THEN
                 BEGIN
                      varVLR_DIF = NEW.VLR_OUTDESP - 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 = 1;
                 END
            END
            
            IF (NEW.VLR_DESC > 0) THEN
            BEGIN
                 UPDATE ITENS_NF  INF
                 SET INF.RAT_DESCTO = (INF.VLR_TOTAL * NEW.VLR_DESC) / :varVLR_ITEM
                 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 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 :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 = 1;
                 END
            END
            
            IF (NEW.VLR_ACRES > 0) THEN
            BEGIN
                 UPDATE ITENS_NF  INF
                 SET INF.RAT_ACRES = (INF.VLR_TOTAL * NEW.VLR_ACRES) / :varVLR_ITEM
                 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 SUM(INF.RAT_ACRES)
                 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 :varVLR_CONFERE;

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

                      UPDATE ITENS_NF  INF
                      SET INF.RAT_ACRES = INF.RAT_ACRES + :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 = 1;
                 END
            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.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;
              
             UPDATE ITENS_NF  INF
             SET INF.RAT_BASE_ICMS = (:varBASE_ICMS2 * INF.VLR_TOTAL) / :varVLR_ITEM,
                 INF.RAT_VLR_ICMS = (:varVLR_ICMS2 * INF.VLR_TOTAL) / :varVLR_ITEM
             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 SUM(INF.RAT_BASE_ICMS), SUM(INF.RAT_VLR_ICMS)
             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 :varVLR_CONFERE, :varVLR_CONFERE2;

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

                  UPDATE ITENS_NF  INF
                  SET INF.RAT_BASE_ICMS = INF.RAT_BASE_ICMS + :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 = 1;
             END
             IF (varVLR_ICMS2 <> varVLR_CONFERE2) THEN
             BEGIN
                  varVLR_DIF = varVLR_ICMS2 - varVLR_CONFERE2;

                  UPDATE ITENS_NF  INF
                  SET INF.RAT_VLR_ICMS = INF.RAT_VLR_ICMS + :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 = 1;
             END
        END

        IF (NEW.DESC_INSS = 'S') THEN
            NEW.VLR_TOTAL = NEW.VLR_TOTAL - NEW.VLR_INSS;
        IF (NEW.DESC_IRRF = 'S') THEN
            NEW.VLR_TOTAL = NEW.VLR_TOTAL - NEW.VLR_IRRF;
END^

SET TERM ;^

COMMIT WORK;

/*================*/
/* Versão: 2.5.32 */
/*==============================================================*/
/* Table: ENTRADAS                                              */
/*==============================================================*/
ALTER TABLE ENTRADAS ADD OPCAO_NF CHAR(2);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_ENTRADAS INACTIVE;

UPDATE ENTRADAS SET OPCAO_NF = 'NO' WHERE OPCAO_NF IS NULL;

ALTER TRIGGER TG_UPDATE_ENTRADAS ACTIVE;

COMMIT WORK;