/*================*/
/* Versão: 3.06   */
/*==============================================================*/
/* Tables: PARCELAS_NF, DESCCORPO_NF, ALIQ_ICMSNF,              */
/*         DESCAUX_NF, ITENS_NF                                 */
/*==============================================================*/
ALTER TABLE PARCELAS_NF DROP CONSTRAINT FK_PARCELAS_NF_NOTA_FISCAL;

ALTER TABLE DESCCORPO_NF DROP CONSTRAINT FK_DESCCORPO_NF_NOTA_FISCAL;

ALTER TABLE ALIQ_ICMSNF DROP CONSTRAINT FK_ALIQ_ICMSNF_NOTAFISCAL;

ALTER TABLE DESCAUX_NF DROP CONSTRAINT FK_DESCAUX_NF_NOTA_FISCAL;

ALTER TABLE ITENS_NF DROP CONSTRAINT FK_ITENS_NF_NOTA_FISCAL;

COMMIT WORK;

/*==============================================================*/
/* Table: PARCELAS_NF                                           */
/*==============================================================*/
ALTER TABLE PARCELAS_NF ADD SERIE_NF CHAR(3);

COMMIT WORK;

UPDATE PARCELAS_NF PNF
SET PNF.SERIE_NF = '0'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = PNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = PNF.TIPO_CONTROL AND
             NF.NRO_NF = PNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO < '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE PARCELAS_NF PNF
SET PNF.SERIE_NF = '1'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = PNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = PNF.TIPO_CONTROL AND
             NF.NRO_NF = PNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO >= '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE PARCELAS_NF SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: DESCCORPO_NF                                          */
/*==============================================================*/
ALTER TABLE DESCCORPO_NF ADD SERIE_NF CHAR(3);

COMMIT WORK;

UPDATE DESCCORPO_NF DNF
SET DNF.SERIE_NF = '0'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = DNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = DNF.TIPO_CONTROL AND
             NF.NRO_NF = DNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO < '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE DESCCORPO_NF DNF
SET DNF.SERIE_NF = '1'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = DNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = DNF.TIPO_CONTROL AND
             NF.NRO_NF = DNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO >= '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE DESCCORPO_NF SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
ALTER TABLE ITENS_NF ADD SERIE_NF CHAR(3);

COMMIT WORK;

UPDATE ITENS_NF INF
SET INF.SERIE_NF = '0'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = INF.COD_EMPRESA AND
             NF.TIPO_CONTROL = INF.TIPO_CONTROL AND
             NF.NRO_NF = INF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO < '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE ITENS_NF INF
SET INF.SERIE_NF = '1'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = INF.COD_EMPRESA AND
             NF.TIPO_CONTROL = INF.TIPO_CONTROL AND
             NF.NRO_NF = INF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO >= '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE ITENS_NF SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: ALIQ_ICMSNF                                           */
/*==============================================================*/
ALTER TABLE ALIQ_ICMSNF ADD SERIE_NF CHAR(3);

COMMIT WORK;

UPDATE ALIQ_ICMSNF ANF
SET ANF.SERIE_NF = '0'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = ANF.COD_EMPRESA AND
             NF.TIPO_CONTROL = ANF.TIPO_CONTROL AND
             NF.NRO_NF = ANF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO < '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE ALIQ_ICMSNF ANF
SET ANF.SERIE_NF = '1'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = ANF.COD_EMPRESA AND
             NF.TIPO_CONTROL = ANF.TIPO_CONTROL AND
             NF.NRO_NF = ANF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO >= '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE ALIQ_ICMSNF SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: DESCAUX_NF                                            */
/*==============================================================*/
ALTER TABLE DESCAUX_NF ADD SERIE_NF CHAR(3);

COMMIT WORK;

UPDATE DESCAUX_NF DNF
SET DNF.SERIE_NF = '0'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = DNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = DNF.TIPO_CONTROL AND
             NF.NRO_NF = DNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO < '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE DESCAUX_NF DNF
SET DNF.SERIE_NF = '1'
WHERE (SELECT NF.NRO_NF
       FROM NOTA_FISCAL NF
       WHERE NF.COD_EMPRESA = DNF.COD_EMPRESA AND
             NF.TIPO_CONTROL = DNF.TIPO_CONTROL AND
             NF.NRO_NF = DNF.NRO_NF AND
             NF.COD_EMPRESA = 1 AND
             NF.DT_EMISSAO >= '12-01-2007') IS NOT NULL;

COMMIT WORK;

UPDATE DESCAUX_NF SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: NOTA_FISCAL                                           */
/*==============================================================*/
ALTER TABLE NOTA_FISCAL ADD SERIE_NF CHAR(3);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL INACTIVE;

COMMIT WORK;

UPDATE NOTA_FISCAL
SET SERIE_NF = '0'
WHERE COD_EMPRESA = 1 AND
      DT_EMISSAO < '12-01-2007';

COMMIT WORK;

UPDATE NOTA_FISCAL
SET SERIE_NF = '1'
WHERE COD_EMPRESA = 1 AND
      DT_EMISSAO >= '12-01-2007';

COMMIT WORK;

UPDATE NOTA_FISCAL SET SERIE_NF = '0' WHERE SERIE_NF IS NULL;

COMMIT WORK;

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

COMMIT WORK;

ALTER TABLE NOTA_FISCAL DROP CONSTRAINT PK_NOTAFISCAL;

COMMIT WORK;

alter table NOTA_FISCAL
   add constraint PK_NOTA_FISCAL primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL ACTIVE;

COMMIT WORK;

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
ALTER TABLE ITENS_NF DROP CONSTRAINT PK_ITENS_NF;

COMMIT WORK;

alter table ITENS_NF
   add constraint PK_ITENS_NF primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF,SEQ_ITEM);

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: ALIQ_ICMSNF                                           */
/*==============================================================*/
ALTER TABLE ALIQ_ICMSNF DROP CONSTRAINT PK_ALIQ_ICMSNF;

COMMIT WORK;

alter table ALIQ_ICMSNF
   add constraint PK_ALIQ_ICMSNF primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF,TIPO_VLR);

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: DESCAUX_NF                                            */
/*==============================================================*/
ALTER TABLE DESCAUX_NF DROP CONSTRAINT PK_DESCAUX_NF;

COMMIT WORK;

alter table DESCAUX_NF
   add constraint PK_DESCAUX_NF primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF,SEQUENCIA);

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: DESCCORPO_NF                                          */
/*==============================================================*/
ALTER TABLE DESCCORPO_NF DROP CONSTRAINT PK_DESCCORPO_NF;

COMMIT WORK;

alter table DESCCORPO_NF
   add constraint PK_DESCCORPO_NF primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF,SEQUENCIA);

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Table: PARCELAS_NF                                           */
/*==============================================================*/
ALTER TABLE PARCELAS_NF DROP CONSTRAINT PK_PARCELAS;

COMMIT WORK;

alter table PARCELAS_NF
   add constraint PK_PARCELAS_NF primary key (COD_EMPRESA,TIPO_CONTROL,NRO_NF,SERIE_NF,SEQ_PARCELAS);

COMMIT WORK;

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

COMMIT WORK;

/*==============================================================*/
/* Trigger: TG_EXCLUI_NOTAFISCAL                                */
/*==============================================================*/

SET TERM ^;

ALTER TRIGGER TG_EXCLUI_NOTAFISCAL
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
   DELETE FROM PARCELAS_NF
   WHERE COD_EMPRESA = OLD.COD_EMPRESA AND
         NRO_NF = OLD.NRO_NF AND
         TIPO_CONTROL = OLD.TIPO_CONTROL AND
         SERIE_NF = OLD.SERIE_NF;

   DELETE FROM ITENS_NF 
   WHERE COD_EMPRESA = OLD.COD_EMPRESA AND
         NRO_NF = OLD.NRO_NF AND
         TIPO_CONTROL = OLD.TIPO_CONTROL AND
         SERIE_NF = OLD.SERIE_NF;

   DELETE FROM DESCCORPO_NF
   WHERE COD_EMPRESA = OLD.COD_EMPRESA AND
         NRO_NF = OLD.NRO_NF AND
         TIPO_CONTROL = OLD.TIPO_CONTROL AND
         SERIE_NF = OLD.SERIE_NF;

   DELETE FROM DESCAUX_NF
   WHERE COD_EMPRESA = OLD.COD_EMPRESA AND
         NRO_NF = OLD.NRO_NF AND
         TIPO_CONTROL = OLD.TIPO_CONTROL AND
         SERIE_NF = OLD.SERIE_NF;

   DELETE FROM ALIQ_ICMSNF
   WHERE COD_EMPRESA = OLD.COD_EMPRESA AND
         NRO_NF = OLD.NRO_NF AND
         TIPO_CONTROL = OLD.TIPO_CONTROL AND
         SERIE_NF = OLD.SERIE_NF;
END^

SET TERM ;^

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 varNRO_NF INTEGER;
    DECLARE VARIABLE varSERIE_NF CHAR(3);
    DECLARE VARIABLE varVLR_TOTAL 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_SERVICO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_PRODUTO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_FRETE DECIMAL(15,2);
    DECLARE VARIABLE varVLR_SEGURO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_OUT_DESP DECIMAL(15,2);
    DECLARE VARIABLE varVLR_DESCTO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ACRES DECIMAL(15,2);
BEGIN
        /* SELEÇÃO DOS VALORES DOS ITENS DE PRODUTOS */
        SELECT 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.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'MERC'
        INTO :varVLR_PRODUTO, :varBASE_ICMS, :varVLR_ICMS, :varVLR_IPI;

        IF (:varVLR_PRODUTO IS NOT NULL) THEN
             NEW.VLR_PROD = :varVLR_PRODUTO;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = :varVLR_ICMS;
        IF (:varVLR_IPI IS NOT NULL) THEN
             NEW.VLR_IPI = :varVLR_IPI;

        /* SELEÇÃO DOS VALORES DOS ITENS DE SERVICO */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'SERV'
        INTO :varVLR_SERVICO, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_SERVICO IS NOT NULL) THEN
             NEW.VLR_SERV = :varVLR_SERVICO;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;
        
        /* SELEÇÃO DOS VALORES DOS ITENS DE FRETE */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'DADO' AND
              INF.TIPO_VALOR = 'FRETE'
        INTO :varVLR_FRETE, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_FRETE IS NOT NULL) THEN
             NEW.VLR_FRETE = :varVLR_FRETE;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;

        /* SELEÇÃO DOS VALORES DOS ITENS DE SEGURO */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'DADO' AND
              INF.TIPO_VALOR = 'SEGURO'
        INTO :varVLR_SEGURO, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_SEGURO IS NOT NULL) THEN
             NEW.VLR_SEGURO = :varVLR_SEGURO;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;

        /* SELEÇÃO DOS VALORES DOS ITENS DE OUTRAS DESPESAS */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'DADO' AND
              INF.TIPO_VALOR = 'OUT_DESP'
        INTO :varVLR_OUT_DESP, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_OUT_DESP IS NOT NULL) THEN
             NEW.VLR_OUTDESP = :varVLR_OUT_DESP;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;

        /* SELEÇÃO DOS VALORES DOS ITENS DE DESCONTO */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'DADO' AND
              INF.TIPO_VALOR = 'DESCTO'
        INTO :varVLR_DESCTO, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_DESCTO IS NOT NULL) THEN
             NEW.VLR_DESC = :varVLR_DESCTO;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;

        /* SELEÇÃO DOS VALORES DOS ITENS DE ACRESCIMO */
        SELECT SUM(INF.VLR_TOTAL),
               SUM(INF.BASE_ICMS),
               SUM(INF.VLR_ICMS)
        FROM ITENS_NF INF
        WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
              INF.NRO_NF = NEW.NRO_NF AND
              INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
              INF.SERIE_NF = NEW.SERIE_NF AND
              INF.TIPO_ITEM = 'DADO' AND
              INF.TIPO_VALOR = 'ACRES'
        INTO :varVLR_ACRES, :varBASE_ICMS, :varVLR_ICMS;

        IF (:varVLR_ACRES IS NOT NULL) THEN
             NEW.VLR_ACRES = :varVLR_ACRES;
        IF (:varBASE_ICMS IS NOT NULL) THEN
             NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS;
        IF (:varVLR_ICMS IS NOT NULL) THEN
             NEW.VLR_ICMS = NEW.VLR_ICMS + :varVLR_ICMS;

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

        IF (varVLR_PRODUTO IS NOT NULL) THEN
        BEGIN
             UPDATE ITENS_NF INF
             SET INF.VLR_RATEIO = (INF.VLR_TOTAL / :varVLR_PRODUTO) * (NEW.VLR_TOTAL)
             WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                  INF.NRO_NF = NEW.NRO_NF AND
                  INF.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                  INF.SERIE_NF = NEW.SERIE_NF AND
                  INF.TIPO_ITEM = 'MERC';

             NEW.VLR_TOTAL = NEW.VLR_TOTAL + NEW.VLR_PROD;
        END
        
        IF (varVLR_SERVICO IS NOT NULL) THEN
             NEW.VLR_TOTAL = NEW.VLR_TOTAL + NEW.VLR_SERV;
        
        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;

/*==============================================================*/
/* Table: PARAMETROS                                            */
/*==============================================================*/
INSERT INTO PARAMETROS
  (COD_PARAMETRO,SEQ_PARAM,NOME_PARAM,DESCR_PARAM,TIPO_PARAM,CLASS_PARAM,VALOR_PARAM)
VALUES
  (1,34,'NRO_SERIE_NF','Série de Impressão das Notas Fiscais','STRING','1','1');

COMMIT WORK;

/*================*/
/* Versão: 3.14   */
/*==============================================================*/
/* Trigger: TG_UPDATE_ENTRADAS                                */
/*==============================================================*/

SET TERM ^;

ALTER TRIGGER TG_UPDATE_ENTRADAS
ACTIVE BEFORE UPDATE POSITION 0
AS
    DECLARE VARIABLE varCOD_EMPRESA INTEGER;
    DECLARE VARIABLE varSEQ_INCLUSAO INTEGER;
    DECLARE VARIABLE varDOCUMENTO VARCHAR(20);
    DECLARE VARIABLE varVLT_TOTAL_ITEM DECIMAL(15,2);
    DECLARE VARIABLE varVLR_RATEIO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varRAT_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varRAT_BASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varRAT_ICMS_SUBST DECIMAL(15,2);
    DECLARE VARIABLE varSEQUENCIA INTEGER;

/* = = Rateia Acrescimos e descontos entre os Itens de Entrada= =*/
BEGIN
     UPDATE ITENS_ENTRADA IET
     SET IET.RAT_VALOR = (NEW.VLR_FRETE * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) +
                         (NEW.VLR_SEGURO * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) +
                         (NEW.VLR_DESPESAS * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) -
                         (NEW.VLR_DESCONTO * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) +
                         (NEW.VLR_ACR_EXTRA * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) -
                         (NEW.VLR_DESC_EXTRA * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC)) +
                         (NEW.VLR_FRETE_CONH * (IET.VLR_TOTAL_ITEM / NEW.VLR_TOTAL_MERC))
     WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
           IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
           IET.DOCUMENTO = NEW.DOCUMENTO AND
           IET.TIPO_CONTROL = NEW.TIPO_CONTROL;

/* = = Faz somatorios de valores dos Itens de Entrada e procura o primeiro item= =*/
        SELECT IET.DOCUMENTO,
               SUM(IET.RAT_VALOR),
               MIN(IET.SEQ_ITEM),
               SUM(IET.BASE_ICMS),
               SUM(IET.VLR_ICMS)
        FROM ITENS_ENTRADA IET
        WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
              IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
              IET.DOCUMENTO = NEW.DOCUMENTO AND
              IET.TIPO_CONTROL = NEW.TIPO_CONTROL
        GROUP BY IET.DOCUMENTO
        INTO :varDOCUMENTO,
             :varVLR_RATEIO,
             :varSEQUENCIA,
             :varBASE_ICMS,
             :varVLR_ICMS;


     /* = = Calcula a diferenca entre a soma dos Itens de Entrada e o valores Totais na Entrada no Rateio
       e grava a diferenca no primeiro item de entrada = =*/
     UPDATE ITENS_ENTRADA IET
     SET IET.RAT_VALOR = IET.RAT_VALOR - (:varVLR_RATEIO - (NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_DESPESAS + NEW.VLR_FRETE_CONH - NEW.VLR_DESCONTO + NEW.VLR_ACR_EXTRA - NEW.VLR_DESC_EXTRA))
     WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
           IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
           IET.DOCUMENTO = NEW.DOCUMENTO AND
           IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
           IET.SEQ_ITEM = :varSEQUENCIA;

     /* = = Rateia Valores de Icms e Base Icms que alem dos Itens = =*/
     UPDATE ITENS_ENTRADA IET
     SET IET.RAT_ICMS = (NEW.VLR_ICMS - :varVLR_ICMS) * (IET.VLR_ICMS / :varVLR_ICMS),
         IET.RAT_BASE_ICMS = (NEW.BASE_ICMS - :varBASE_ICMS) * (IET.BASE_ICMS / :varBASE_ICMS)
     WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
           IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
           IET.DOCUMENTO = NEW.DOCUMENTO AND
           IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
           IET.VLR_ICMS > 0;

     /* = = Faz somatorio do Icms e Base Icms e procura o primeiro item com ICMS = =*/
     SELECT IET.DOCUMENTO,
            MIN(IET.SEQ_ITEM),
            SUM(IET.RAT_ICMS),
            SUM(IET.RAT_BASE_ICMS)
     FROM ITENS_ENTRADA IET
     WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
           IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
           IET.DOCUMENTO = NEW.DOCUMENTO AND
           IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
           IET.VLR_ICMS > 0
     GROUP BY IET.DOCUMENTO
     INTO :varDOCUMENTO,
          :varSEQUENCIA,
          :varRAT_ICMS,
          :varRAT_BASE_ICMS;

     IF (varRAT_ICMS IS NOT NULL) THEN
     BEGIN
          /* = = Grava a diferenca no primeiro item de entrada com ICMS= =*/
          UPDATE ITENS_ENTRADA IET
          SET IET.RAT_ICMS = IET.RAT_ICMS + (NEW.VLR_ICMS - (:varRAT_ICMS + :varVLR_ICMS)),
              IET.RAT_BASE_ICMS = IET.RAT_BASE_ICMS + (NEW.BASE_ICMS - (:varRAT_BASE_ICMS + :varBASE_ICMS))
          WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
                IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
                IET.DOCUMENTO = NEW.DOCUMENTO AND
                IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                IET.SEQ_ITEM = :varSEQUENCIA;
     END

     /* == TRATA SUBSTITUIÇÃO TRIBUTÁRIA  */

     IF (NEW.ICMS_SUBST > 0) THEN
     BEGIN
          /* = = Faz somatorios de valores dos Itens de Entrada que tem substituição */
          SELECT SUM(IET.VLR_TOTAL_ITEM)
          FROM ITENS_ENTRADA IET
          WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
                IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
                IET.DOCUMENTO = NEW.DOCUMENTO AND
                IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                (IET.CST_TRIB = '10' OR IET.CST_TRIB = '30' OR IET.CST_TRIB = '60' OR IET.CST_TRIB = '70')
          GROUP BY IET.COD_EMPRESA, IET.DOCUMENTO
          INTO :varVLT_TOTAL_ITEM;

          /* ==  Passa todos os itens que tem susbstituição e rateia o valor da mesma */
          UPDATE ITENS_ENTRADA IET
          SET IET.RAT_ICMS_SUBST = (NEW.ICMS_SUBST * (IET.VLR_TOTAL_ITEM / :varVLT_TOTAL_ITEM))
          WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
                IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
                IET.DOCUMENTO = NEW.DOCUMENTO AND
                IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                (IET.CST_TRIB = '10' OR IET.CST_TRIB = '30' OR IET.CST_TRIB = '60' OR IET.CST_TRIB = '70');

          /* = = Faz somatorio do Icms e Base Icms e procura o primeiro item com ICMS = =*/
          SELECT IET.DOCUMENTO,
                 MIN(IET.SEQ_ITEM),
                 SUM(IET.RAT_ICMS_SUBST)
          FROM ITENS_ENTRADA IET
          WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
                IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
                IET.DOCUMENTO = NEW.DOCUMENTO AND
                IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                IET.RAT_ICMS_SUBST > 0 AND
                (IET.CST_TRIB = '10' OR IET.CST_TRIB = '30' OR IET.CST_TRIB = '60' OR IET.CST_TRIB = '70')
          GROUP BY IET.DOCUMENTO
          INTO :varDOCUMENTO,
               :varSEQUENCIA,
               :varRAT_ICMS_SUBST;

          /* = = Grava a diferenca no primeiro item de entrada com ICMS= =*/
          UPDATE ITENS_ENTRADA IET
          SET IET.RAT_ICMS_SUBST = IET.RAT_ICMS_SUBST + (NEW.ICMS_SUBST - :varRAT_ICMS_SUBST)
          WHERE IET.COD_EMPRESA = NEW.COD_EMPRESA AND
                IET.SEQ_INCLUSAO = NEW.SEQ_INCLUSAO AND
                IET.DOCUMENTO = NEW.DOCUMENTO AND
                IET.TIPO_CONTROL = NEW.TIPO_CONTROL AND
                IET.SEQ_ITEM = :varSEQUENCIA;
     END
END^

SET TERM ;^

COMMIT WORK;