/*==============*/
/* Versão: 3.33 */
/*==============================================================*/
/* Table: ALIQ_COFINS                                           */
/*==============================================================*/
create table ALIQ_COFINS (
COD_COFINS           SMALLINT                       not null,
DESCRICAO            VARCHAR(45),
CST                  CHAR(2),
BASE_COFINS          NUMERIC(18,3),
PERC_COFINS          NUMERIC(9,4),
STATUS_ALT           CHAR(1),
constraint PK_ALIQ_COFINS primary key (COD_COFINS)
);

/*==============================================================*/
/* Table: ALIQ_PIS                                              */
/*==============================================================*/
create table ALIQ_PIS (
COD_PIS              SMALLINT                       not null,
DESCRICAO            VARCHAR(45),
CST                  CHAR(2),
BASE_PIS             NUMERIC(18,3),
PERC_PIS             NUMERIC(9,4),
STATUS_ALT           CHAR(1),
constraint PK_ALIQ_PIS primary key (COD_PIS)
);

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
alter table ITENS_NF add COD_PIS      SMALLINT;
alter table ITENS_NF add BASE_PIS     NUMERIC(15,2);
alter table ITENS_NF add PERC_PIS     NUMERIC(9,4);
alter table ITENS_NF add VLR_PIS      NUMERIC(15,2);
alter table ITENS_NF add CST_PIS      CHAR(2);
alter table ITENS_NF add COD_COFINS   SMALLINT;
alter table ITENS_NF add BASE_COFINS  NUMERIC(15,2);
alter table ITENS_NF add PERC_COFINS  NUMERIC(9,4);
alter table ITENS_NF add VLR_COFINS   NUMERIC(15,2);
alter table ITENS_NF add CST_COFINS   CHAR(2);

COMMIT WORK;

alter table ITENS_NF
   add constraint FK_ITENSNF_ALIQCOFINS foreign key (COD_COFINS)
      references ALIQ_COFINS (COD_COFINS);

alter table ITENS_NF
   add constraint FK_ITENSNF_ALIQPIS foreign key (COD_PIS)
      references ALIQ_PIS (COD_PIS);

COMMIT WORK;

/*==============================================================*/
/* Table: PRODUTOS                                              */
/*==============================================================*/
alter table PRODUTOS add COD_PIS     SMALLINT;
alter table PRODUTOS add COD_COFINS  SMALLINT;

COMMIT WORK;

alter table PRODUTOS
   add constraint FK_PRODUTOS_ALIQCOFINS foreign key (COD_COFINS)
      references ALIQ_COFINS (COD_COFINS);

alter table PRODUTOS
   add constraint FK_PRODUTOS_ALIQPIS foreign key (COD_PIS)
      references ALIQ_PIS (COD_PIS);

COMMIT WORK;

/*==============================================================*/
/* Table: NOTAFISCAL                                            */
/*==============================================================*/
ALTER TABLE NOTAFISCAL ADD REF_INDOPERACAO  CHAR(1);
ALTER TABLE NOTAFISCAL ADD REF_INDTPEMISSAO CHAR(1);
ALTER TABLE NOTAFISCAL ADD REF_CLIEMITE     INTEGER;
ALTER TABLE NOTAFISCAL ADD REF_EMISSAODOC   DATE;

COMMIT WORK;

ALTER TABLE NOTAFISCAL ADD VLR_PIS    NUMERIC(15,2);
ALTER TABLE NOTAFISCAL ADD VLR_COFINS NUMERIC(15,2);

COMMIT WORK;

/*==============================================================*/
/* Table: PARAMETROS                                            */
/*==============================================================*/
INSERT INTO PARAMETROS
  (COD_PARAMETRO,SEQ_PARAM,NOME_PARAM,DESCR_PARAM,TIPO_PARAM,CLASS_PARAM,VALOR_PARAM)
VALUES
  (1,20,'GER_TESTA_VALIDA_SPED','Gerencial: Testar e Validar dados para SPED','STRING','1','S');

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 varNRO_NF INTEGER;
    DECLARE VARIABLE varVLR_ITENS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CALC DECIMAL(15,2);
    DECLARE VARIABLE varVLR_FUNRURAL DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ROYALTIES DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS 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 varVLR_DIF DECIMAL(15,2);
    DECLARE VARIABLE varSEQ_PRIM_ITEM INTEGER;
    DECLARE VARIABLE varVLR_PIS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_COFINS DECIMAL(15,2);

BEGIN
     SELECT SUM(INF.VLR_TOTAL),
            SUM(INF.BASE_ICMS),
            SUM(INF.VLR_ICMS),
            SUM(INF.VLR_PIS),
            SUM(INF.VLR_COFINS)
     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
     GROUP BY INF.COD_EMPRESA, INF.NRO_NF
     INTO :varVLR_ITENS,
          :varBASE_ICMS,
          :varVLR_ICMS,
          :varVLR_PIS,
          :varVLR_COFINS;

     NEW.VLR_TOTAL = NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP - NEW.VLR_DESC;
     varVLR_CALC = NEW.VLR_TOTAL;
     
     IF (varVLR_PIS IS NULL) THEN
          varVLR_PIS = 0;
     IF (varVLR_COFINS IS NULL) THEN
          varVLR_COFINS = 0;
          
     NEW.VLR_PIS = varVLR_PIS;
     NEW.VLR_COFINS = varVLR_COFINS;

     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) * :varVLR_CALC
          WHERE INF.COD_EMPRESA = NEW.COD_EMPRESA AND
                INF.SERIE_NF = NEW.SERIE_NF AND
                INF.NRO_NF = NEW.NRO_NF;

          NEW.VLR_PROD  = :varVLR_ITENS;
          NEW.BASE_ICMS = :varBASE_ICMS;
          NEW.VLR_ICMS  = :varVLR_ICMS;
          NEW.VLR_TOTAL = NEW.VLR_TOTAL + :varVLR_ITENS;
        
          varVLR_CALC = NEW.VLR_PROD;
          NEW.VLR_FUNRURAL = 0;
          IF (NEW.PERC_FUNRURAL > 0) THEN
          BEGIN
               varVLR_FUNRURAL = (varVLR_CALC * NEW.PERC_FUNRURAL) / 100;
               NEW.VLR_TOTAL = NEW.VLR_TOTAL - varVLR_FUNRURAL;
               NEW.VLR_FUNRURAL = varVLR_FUNRURAL;
          END

          IF (NEW.APL_DESC_LIQUIDO = 'S') THEN
               varVLR_CALC = varVLR_CALC - NEW.VLR_FUNRURAL;

          IF (NEW.ROYALTIES_MANUAL = 'N') THEN
          BEGIN
               NEW.VLR_ROYALTIES = 0;
               IF (NEW.PERC_ROYALTIES > 0) THEN
               BEGIN
                    varVLR_ROYALTIES = (varVLR_CALC * NEW.PERC_ROYALTIES) / 100;
                    NEW.VLR_TOTAL = NEW.VLR_TOTAL - varVLR_ROYALTIES;
                    NEW.VLR_ROYALTIES = varVLR_ROYALTIES;
               END
          END
          
          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;

               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
               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.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;

               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
               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.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

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

               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
               INTO :varSEQ_PRIM_ITEM, :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.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;

               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 
               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.SEQUENCIA = :varSEQ_PRIM_ITEM;
               END
          END

     END

     SELECT ALF.COD_EMPRESA,
            ALF.NRO_NF,
            SUM(ALF.BASE_ICMS),
            SUM(ALF.VLR_ICMS)
     FROM ALIQ_ICMSNF ALF
     WHERE ALF.COD_EMPRESA = NEW.COD_EMPRESA AND
           ALF.SERIE_NF = NEW.SERIE_NF AND
           ALF.NRO_NF = NEW.NRO_NF
     GROUP BY ALF.COD_EMPRESA, ALF.NRO_NF
     INTO :varCOD_EMPRESA,
          :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;