/*==============================================================*/
/*                                                              */
/*                    I M P O R T A N T E                       */
/*                                                              */
/*               CONTINUAÇÃO DA ALTERAÇÃO 78.1                  */
/*==============================================================*/

/*==============================================================*/
/* Table: OPER_FISCAL                                           */
/*==============================================================*/
alter table OPER_FISCAL 
   add constraint PK_OPER_FISCAL primary key (COD_OPEFISC);

COMMIT WORK;

UPDATE OPER_FISCAL_ALIQ OPA
SET OPA.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = OPA.CFOP);

alter table OPER_FISCAL_ALIQ
   add constraint PK_OPER_FISCAL_ALIQ primary key (COD_OPEFISC,COD_ICMS);

alter table OPER_FISCAL_ALIQ
   add constraint FK_OPER_FISCAL_ALIQ_OPER_FISCAL foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);


COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL INACTIVE;

COMMIT WORK;

ALTER TABLE NOTAFISCAL ADD DESCR_CFOP VARCHAR(25);
UPDATE NOTAFISCAL SET DESCR_CFOP = CFOP WHERE DESCR_CFOP IS NULL;

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_NOTAFISCAL ACTIVE;

COMMIT WORK;

UPDATE ITENS_NF INF
SET INF.CFOP = (SELECT NF.CFOP
                FROM NOTAFISCAL NF
                WHERE NF.COD_EMPRESA = INF.COD_EMPRESA AND
                      NF.SERIE_NF = INF.SERIE_NF AND
                      NF.TIPO_CONTROL = INF.TIPO_CONTROL AND
                      NF.NRO_NF = INF.NRO_NF);

UPDATE SERVICOS_NF SNF
SET SNF.CFOP = (SELECT NF.CFOP
                FROM NOTAFISCAL NF
                WHERE NF.COD_EMPRESA = SNF.COD_EMPRESA AND
                      NF.SERIE_NF = SNF.SERIE_NF AND
                      NF.TIPO_CONTROL = SNF.TIPO_CONTROL AND
                      NF.NRO_NF = SNF.NRO_NF);

COMMIT WORK;

ALTER TABLE NOTAFISCAL ALTER COLUMN DESC_OPERACAO TYPE VARCHAR (80) CHARACTER SET ISO8859_1;

UPDATE ITENS_NF INF
SET INF.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = INF.CFOP);

UPDATE SERVICOS_NF SNF
SET SNF.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = SNF.CFOP);

COMMIT WORK;

alter table ITENS_NF
   add constraint FK_ITENS_NF_OPER_FISCAL foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);

alter table SERVICOS_NF
   add constraint FK_SERVICOS_NF_OPER_FISCAL foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);

COMMIT WORK;

UPDATE ITENS_ENTRADA IET
SET IET.CFOP = (SELECT ET.CFOP
                FROM ENTRADAS ET
                WHERE ET.COD_EMPRESA = IET.COD_EMPRESA AND
                      ET.SEQ_INCLUSAO = IET.SEQ_INCLUSAO AND
                      ET.TIPO_CONTROL = IET.TIPO_CONTROL AND
                      ET.DOCUMENTO = IET.DOCUMENTO);

COMMIT WORK;

UPDATE ITENS_ENTRADA IET
SET IET.COD_OPEFISC = (SELECT OP.COD_OPEFISC
                       FROM OPER_FISCAL OP
                       WHERE OP.CFOP = IET.CFOP);

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_ENTRADAS INACTIVE;

COMMIT WORK;

UPDATE ENTRADAS ET
SET ET.DESC_OPERACAO = (SELECT OP.DESCRICAO
                        FROM OPER_FISCAL OP
                        WHERE OP.CFOP = ET.CFOP)
WHERE ET.DESC_OPERACAO IS NULL;

COMMIT WORK;

ALTER TRIGGER TG_UPDATE_ENTRADAS ACTIVE;

COMMIT WORK;

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

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

alter table ITENS_ENTRADA
   add constraint FK_ITENS_ENTRADA_OPER_FISCAL foreign key (COD_OPEFISC) references OPER_FISCAL(COD_OPEFISC);

COMMIT WORK;

/*==============================================================*/
/*                                                              */
/*                   A C A B A    A Q U I                       */
/*                                                              */
/*==============================================================*/


/*=================*/
/* Versão: 2.5.25a */
/*==============================================================*/
/* Table: PARAMETROS                                            */
/*==============================================================*/
UPDATE PARAMETROS
SET NOME_PARAM = 'VENDA_COD_LCTO_CONTAS',
    DESCR_PARAM = 'Vendas: Cod. Lancamento Inclusão de Contas Receber'
WHERE SEQ_PARAM = 8;

DELETE FROM PARAMETROS WHERE SEQ_PARAM = 9;

INSERT INTO PARAMETROS
  (COD_PARAMETRO,SEQ_PARAM,NOME_PARAM,DESCR_PARAM,TIPO_PARAM,CLASS_PARAM,VALOR_PARAM)
VALUES
  (1,9,'COMPRA_COD_LCTO_CONTAS','Compras: Cod. Lancamento Inclusão de Contas Pagar','INTEGER','7',NULL);


COMMIT WORK;

/*==============================================================*/
/* Table: ITENS_NF                                              */
/*==============================================================*/
ALTER TABLE ITENS_NF ADD RAT_FRETE     NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_DESPESAS  NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_SEGURO    NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_DESCTO    NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_ACRES     NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_BASE_ICMS NUMERIC(15,2);
ALTER TABLE ITENS_NF ADD RAT_VLR_ICMS  NUMERIC(15,2);

COMMIT WORK;

UPDATE ITENS_NF SET RAT_FRETE = 0 WHERE RAT_FRETE IS NULL;
UPDATE ITENS_NF SET RAT_DESPESAS = 0 WHERE RAT_DESPESAS IS NULL;
UPDATE ITENS_NF SET RAT_SEGURO = 0 WHERE RAT_SEGURO IS NULL;
UPDATE ITENS_NF SET RAT_DESCTO = 0 WHERE RAT_DESCTO IS NULL;
UPDATE ITENS_NF SET RAT_ACRES = 0 WHERE RAT_ACRES IS NULL;
UPDATE ITENS_NF SET RAT_BASE_ICMS = 0 WHERE RAT_BASE_ICMS IS NULL;
UPDATE ITENS_NF SET RAT_VLR_ICMS = 0 WHERE RAT_VLR_ICMS IS NULL;

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_TOTAL 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_TOTAL, :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_TOTAL IS NULL) THEN
            varVLR_TOTAL = 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;

        IF ((varVLR_TOTAL IS NOT NULL) AND (varVLR_TOTAL > 0)) THEN
        BEGIN
            UPDATE ITENS_NF  INF
            SET INF.VLR_RATEIO = (INF.VLR_TOTAL / :varVLR_TOTAL) *
               (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_TOTAL;
            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_TOTAL + :varVLR_SERV + 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_TOTAL
                 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_TOTAL
                 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_TOTAL
                 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_TOTAL
                 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_TOTAL
                 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_TOTAL,
                 INF.RAT_VLR_ICMS = (:varVLR_ICMS2 * INF.VLR_TOTAL) / :varVLR_TOTAL
             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;

/*==============================================================*/
/* Table: ITENS_ENTRADA                                         */
/*==============================================================*/
ALTER TABLE ITENS_ENTRADA ADD RAT_FRETE    NUMERIC(15,2);
ALTER TABLE ITENS_ENTRADA ADD RAT_DESPESAS NUMERIC(15,2);
ALTER TABLE ITENS_ENTRADA ADD RAT_SEGURO   NUMERIC(15,2);
ALTER TABLE ITENS_ENTRADA ADD RAT_DESCTO   NUMERIC(15,2);

COMMIT WORK;

UPDATE ITENS_ENTRADA SET RAT_FRETE = 0 WHERE RAT_FRETE IS NULL;
UPDATE ITENS_ENTRADA SET RAT_DESPESAS = 0 WHERE RAT_DESPESAS IS NULL;
UPDATE ITENS_ENTRADA SET RAT_SEGURO = 0 WHERE RAT_SEGURO IS NULL;
UPDATE ITENS_ENTRADA SET RAT_DESCTO = 0 WHERE RAT_DESCTO IS NULL;

COMMIT WORK;

/*==============================================================*/
/* 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 varVLR_RATEIO DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_IPI DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varRAT_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varRAT_IPI DECIMAL(15,2);
    DECLARE VARIABLE varRAT_BASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varSEQUENCIA INTEGER;
    DECLARE VARIABLE varVLT_TOTAL_ITEM DECIMAL(15,2);
    DECLARE VARIABLE varVLR_CONFERE DECIMAL(15,2);
    DECLARE VARIABLE varVLR_DIF DECIMAL(15,2);
    DECLARE VARIABLE varVLR_TOTAL_DESCTO DECIMAL(15,2);

BEGIN
     /* = = Rateia Acrescimos e descontos entre os Itens de Entrada= =*/
     IF ((NEW.VLR_TOTAL_MERC IS NOT NULL) AND (NEW.VLR_TOTAL_MERC > 0)) THEN
     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;

          IF (NEW.VLR_FRETE > 0) THEN
          BEGIN
               UPDATE ITENS_ENTRADA IET
               SET IET.RAT_FRETE = (IET.VLR_TOTAL_ITEM * NEW.VLR_FRETE) / 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;

               SELECT SUM(IET.RAT_FRETE)
               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
               INTO :varVLR_CONFERE;

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

                    UPDATE ITENS_ENTRADA  IET
                    SET IET.RAT_FRETE = IET.RAT_FRETE + :varVLR_DIF
                    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.SEQUENCIA = 1;
               END
          END
          
          IF (NEW.VLR_SEGURO > 0) THEN
          BEGIN
               UPDATE ITENS_ENTRADA IET
               SET IET.RAT_SEGURO = (IET.VLR_TOTAL_ITEM * NEW.VLR_SEGURO) / 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;

               SELECT SUM(IET.RAT_SEGURO)
               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
               INTO :varVLR_CONFERE;

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

                    UPDATE ITENS_ENTRADA  IET
                    SET IET.RAT_SEGURO = IET.RAT_SEGURO + :varVLR_DIF
                    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.SEQUENCIA = 1;
               END
          END
          
          IF (NEW.VLR_DESPESAS > 0) THEN
          BEGIN
               UPDATE ITENS_ENTRADA IET
               SET IET.RAT_DESPESAS = (IET.VLR_TOTAL_ITEM * NEW.VLR_DESPESAS) / 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;

               SELECT SUM(IET.RAT_DESPESAS)
               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
               INTO :varVLR_CONFERE;

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

                    UPDATE ITENS_ENTRADA  IET
                    SET IET.RAT_DESPESAS = IET.RAT_DESPESAS + :varVLR_DIF
                    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.SEQUENCIA = 1;
               END
          END
          
          varVLR_TOTAL_DESCTO = NEW.VLR_DESCONTO + NEW.VLR_DESC_EXTRA;
          IF (varVLR_TOTAL_DESCTO > 0) THEN
          BEGIN
               UPDATE ITENS_ENTRADA IET
               SET IET.RAT_DESCTO = (IET.VLR_TOTAL_ITEM * :varVLR_TOTAL_DESCTO) / 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;

               SELECT SUM(IET.RAT_DESCTO)
               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
               INTO :varVLR_CONFERE;

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

                    UPDATE ITENS_ENTRADA  IET
                    SET IET.RAT_DESCTO = IET.RAT_DESCTO + :varVLR_DIF
                    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.SEQUENCIA = 1;
               END
          END
     END

     /* = = Faz somatorios de valores dos Itens de Entrada e procura o primeiro item= =*/
     SELECT IET.COD_EMPRESA,
            IET.SEQ_INCLUSAO,
            IET.DOCUMENTO,
            SUM(IET.RAT_VALOR),
            MIN(IET.SEQUENCIA),
            SUM(IET.BASE_ICMS),
            SUM(IET.VLR_ICMS),
            SUM(IET.VLR_IPI)
     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.COD_EMPRESA, IET.SEQ_INCLUSAO, IET.DOCUMENTO
     INTO :varCOD_EMPRESA,
          :varSEQ_INCLUSAO,
          :varDOCUMENTO,
          :varVLR_RATEIO,
          :varSEQUENCIA,
          :varBASE_ICMS,
          :varVLR_ICMS,
          :varVLR_IPI;

     /* = = 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.SEQUENCIA = :varSEQUENCIA;

     /* ========= TRATA ICMS ================================================================== */
     /* = = Rateia Valores de Icms e Base Icms que alem dos Itens = =*/
     IF ((varVLR_ICMS > 0) AND (varBASE_ICMS > 0)) THEN
     BEGIN
          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.COD_EMPRESA,
                 IET.SEQ_INCLUSAO,
                 IET.DOCUMENTO,
                 MIN(IET.SEQUENCIA),
                 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.COD_EMPRESA, IET.SEQ_INCLUSAO, IET.DOCUMENTO
          INTO :varCOD_EMPRESA,
               :varSEQ_INCLUSAO,
               :varDOCUMENTO,
               :varSEQUENCIA,
               :varRAT_ICMS,
               :varRAT_BASE_ICMS;

          /* = = 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.SEQUENCIA = :varSEQUENCIA;
     END
     /* ============ TRATA IPI ============================================================= */
     /* = = Rateia Valores de IPI que alem dos Itens = =*/
     IF (varVLR_IPI > 0) THEN
     BEGIN
          UPDATE ITENS_ENTRADA IET
          SET IET.RAT_IPI = (NEW.VLR_IPI - :varVLR_IPI) * (IET.VLR_IPI / :varVLR_IPI)
          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_IPI > 0;

          /* = = Faz somatorio do IPI e procura o primeiro item com IPI = =*/
          SELECT IET.COD_EMPRESA,
                 IET.SEQ_INCLUSAO,
                 IET.DOCUMENTO,
                 MIN(IET.SEQUENCIA),
                 SUM(IET.RAT_IPI)
          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_IPI > 0
          GROUP BY IET.COD_EMPRESA, IET.SEQ_INCLUSAO, IET.DOCUMENTO
          INTO :varCOD_EMPRESA,
               :varSEQ_INCLUSAO,
               :varDOCUMENTO,
               :varSEQUENCIA,
               :varRAT_IPI;

          /* = = Grava a diferenca no primeiro item de entrada com IPI = =*/
          UPDATE ITENS_ENTRADA IET
          SET IET.RAT_IPI = IET.RAT_IPI + (NEW.VLR_IPI - (:varRAT_IPI + :varVLR_IPI))
          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.SEQUENCIA = :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 e procura o primeiro item= =*/
          SELECT IET.COD_EMPRESA,
                 IET.SEQ_INCLUSAO,
                 IET.DOCUMENTO,
                 MIN(IET.SEQUENCIA),
                 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.SEQ_INCLUSAO, IET.DOCUMENTO
          INTO :varCOD_EMPRESA,
               :varSEQ_INCLUSAO,
               :varDOCUMENTO,
               :varSEQUENCIA,
               :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');
     END
END^

SET TERM ;^

COMMIT WORK;