SET TERM ^ ;

CREATE OR ALTER TRIGGER TRG_NFE_PROD_IBSCBS_INS
FOR NFE_PRODUTO
ACTIVE BEFORE INSERT POSITION 0
AS
  DECLARE VARIABLE V_CST       VARCHAR(5);
  DECLARE VARIABLE V_CCLAS     VARCHAR(10);
  DECLARE VARIABLE V_AIBS      NUMERIC(8,4);
  DECLARE VARIABLE V_ACBS      NUMERIC(8,4);
  DECLARE VARIABLE V_AIBS_MUN  NUMERIC(8,4);
  DECLARE VARIABLE V_ISIBS     CHAR(1);
  DECLARE VARIABLE V_ISCBS     CHAR(1);
  DECLARE VARIABLE V_REDIBS    NUMERIC(8,4);
  DECLARE VARIABLE V_REDCBS    NUMERIC(8,4);
  DECLARE VARIABLE V_TT        INTEGER;
  DECLARE VARIABLE V_BASE      NUMERIC(18,6);
  DECLARE VARIABLE V_REDFACTOR NUMERIC(18,6);
  DECLARE VARIABLE V_ALIQEFET  NUMERIC(18,6);
BEGIN
  V_CST      = ''; V_CCLAS    = ''; V_AIBS     = 0; V_ACBS     = 0;
  V_AIBS_MUN = 0; V_ISIBS    = 'N'; V_ISCBS    = 'N'; V_REDIBS   = 0;
  V_REDCBS   = 0; V_TT       = NULL;

  IF (NEW.CFOP_PRODUTO IS NOT NULL AND TRIM(NEW.CFOP_PRODUTO) <> '') THEN
  BEGIN
    SELECT
      COALESCE(C.CST_IBSCBS, ''), COALESCE(C.CCLASSTRIB, ''),
      COALESCE(C.ALIQ_IBS, 0), COALESCE(C.ALIQ_CBS, 0), COALESCE(C.ALIQ_IBS_MUN, 0),
      COALESCE(C.ISENTO_IBS, 'N'), COALESCE(C.ISENTO_CBS, 'N'),
      COALESCE(C.RED_IBS, 0), COALESCE(C.RED_CBS, 0)
    FROM CFOP C
    WHERE C.CODIGO_CFOP = NEW.CFOP_PRODUTO
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF (((V_CST = '') OR (V_CCLAS = '')) AND (NEW.CODIGO_PRODUTO IS NOT NULL)) THEN
  BEGIN
    SELECT
      COALESCE(P.CST_IBSCBS, ''), COALESCE(P.CCLASSTRIB, ''),
      COALESCE(P.ALIQ_IBS, 0), COALESCE(P.ALIQ_CBS, 0), COALESCE(P.ALIQ_IBS_MUN, 0),
      COALESCE(P.ISENTO_IBS, 'N'), COALESCE(P.ISENTO_CBS, 'N'),
      COALESCE(P.RED_IBS, 0), COALESCE(P.RED_CBS, 0), P.CODIGO_TIPOTRIBUTACAO
    FROM PRODUTO P
    WHERE P.CODIGO_EMPRESA = NEW.CODIGO_EMPRESA AND P.CODIGO_PRODUTO = NEW.CODIGO_PRODUTO
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS, :V_TT;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF (((V_CST = '') OR (V_CCLAS = '')) AND (V_TT IS NOT NULL)) THEN
  BEGIN
    SELECT
      COALESCE(T.CST_IBSCBS, ''), COALESCE(T.CCLASSTRIB, ''),
      COALESCE(T.ALIQ_IBS, 0), COALESCE(T.ALIQ_CBS, 0), COALESCE(T.ALIQ_IBS_MUN, 0),
      COALESCE(T.ISENTO_IBS, 'N'), COALESCE(T.ISENTO_CBS, 'N'),
      COALESCE(T.RED_IBS, 0), COALESCE(T.RED_CBS, 0)
    FROM TIPOTRIBUTACAO T
    WHERE T.CODIGO_EMPRESA = NEW.CODIGO_EMPRESA AND T.CODIGO_TIPOTRIBUTACAO = :V_TT
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF ((V_CST = '') OR (V_CCLAS = '')) THEN EXIT;

  IF (CHAR_LENGTH(V_CST) = 1) THEN V_CST = '00' || V_CST;
  ELSE IF (CHAR_LENGTH(V_CST) = 2) THEN V_CST = '0' || V_CST;

  IF (CHAR_LENGTH(V_CCLAS) = 1) THEN V_CCLAS = '00000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 2) THEN V_CCLAS = '0000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 3) THEN V_CCLAS = '000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 4) THEN V_CCLAS = '00' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 5) THEN V_CCLAS = '0' || V_CCLAS;

  V_BASE = COALESCE(NEW.VALORBRUTO_PRODUTO, 0) - COALESCE(NEW.DESCONTO_PRODUTO, 0) + COALESCE(NEW.TOTFRETE_PRODUTO, 0) + COALESCE(NEW.TOTSEGURO_PRODUTO, 0) + COALESCE(NEW.TOTOUTRO_PRODUTO, 0);
  IF (V_BASE < 0) THEN V_BASE = 0;

  IF (NEW.CST_IBSCBS IS NULL OR TRIM(NEW.CST_IBSCBS) = '') THEN NEW.CST_IBSCBS = V_CST;
  IF (NEW.CCLASSTRIB IS NULL OR TRIM(NEW.CCLASSTRIB) = '') THEN NEW.CCLASSTRIB = V_CCLAS;
  IF (NEW.ISENTO_IBS IS NULL OR TRIM(NEW.ISENTO_IBS) = '') THEN NEW.ISENTO_IBS = COALESCE(V_ISIBS,'N');
  IF (NEW.ISENTO_CBS IS NULL OR TRIM(NEW.ISENTO_CBS) = '') THEN NEW.ISENTO_CBS = COALESCE(V_ISCBS,'N');
  IF (NEW.RED_IBS IS NULL) THEN NEW.RED_IBS = COALESCE(V_REDIBS,0);
  IF (NEW.RED_CBS IS NULL) THEN NEW.RED_CBS = COALESCE(V_REDCBS,0);
  IF (NEW.ALIQ_IBS IS NULL) THEN NEW.ALIQ_IBS = COALESCE(V_AIBS,0);
  IF (NEW.ALIQ_IBS_MUN IS NULL) THEN NEW.ALIQ_IBS_MUN = COALESCE(V_AIBS_MUN,0);
  IF (NEW.ALIQ_CBS IS NULL) THEN NEW.ALIQ_CBS = COALESCE(V_ACBS,0);

  NEW.BASE_IBS = V_BASE; NEW.BASE_CBS = V_BASE;

  IF (NEW.ISENTO_IBS = 'S') THEN
  BEGIN
    NEW.VLR_IBS = 0; NEW.BASE_IBS = 0;
  END
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_IBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_IBS,0) * V_REDFACTOR;
    NEW.VLR_IBS = (V_BASE * V_ALIQEFET) / 100;
  END
  IF (NEW.ISENTO_IBS = 'S') THEN NEW.VLR_IBS_MUN = 0;
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_IBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_IBS_MUN,0) * V_REDFACTOR;
    NEW.VLR_IBS_MUN = (V_BASE * V_ALIQEFET) / 100;
  END
  IF (NEW.ISENTO_CBS = 'S') THEN
  BEGIN
    NEW.VLR_CBS = 0; NEW.BASE_CBS = 0;
  END
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_CBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_CBS,0) * V_REDFACTOR;
    NEW.VLR_CBS = (V_BASE * V_ALIQEFET) / 100;
  END
END^

CREATE OR ALTER TRIGGER TRG_NFCE_PROD_IBSCBS_INS
FOR NFCE_PRODUTO
ACTIVE BEFORE INSERT POSITION 0
AS
  DECLARE VARIABLE V_CST       VARCHAR(5);
  DECLARE VARIABLE V_CCLAS     VARCHAR(10);
  DECLARE VARIABLE V_AIBS      NUMERIC(8,4);
  DECLARE VARIABLE V_ACBS      NUMERIC(8,4);
  DECLARE VARIABLE V_AIBS_MUN  NUMERIC(8,4);
  DECLARE VARIABLE V_ISIBS     CHAR(1);
  DECLARE VARIABLE V_ISCBS     CHAR(1);
  DECLARE VARIABLE V_REDIBS    NUMERIC(8,4);
  DECLARE VARIABLE V_REDCBS    NUMERIC(8,4);
  DECLARE VARIABLE V_TT        INTEGER;
  DECLARE VARIABLE V_BASE      NUMERIC(18,6);
  DECLARE VARIABLE V_REDFACTOR NUMERIC(18,6);
  DECLARE VARIABLE V_ALIQEFET  NUMERIC(18,6);
BEGIN
  V_CST      = '';
  V_CCLAS    = '';
  V_AIBS     = 0;
  V_ACBS     = 0;
  V_AIBS_MUN = 0;
  V_ISIBS    = 'N';
  V_ISCBS    = 'N';
  V_REDIBS   = 0;
  V_REDCBS   = 0;
  V_TT       = NULL;

  IF (NEW.CFOP_PRODUTO IS NOT NULL) THEN
  BEGIN
    SELECT
      COALESCE(C.CST_IBSCBS, ''), COALESCE(C.CCLASSTRIB, ''),
      COALESCE(C.ALIQ_IBS, 0), COALESCE(C.ALIQ_CBS, 0), COALESCE(C.ALIQ_IBS_MUN, 0),
      COALESCE(C.ISENTO_IBS, 'N'), COALESCE(C.ISENTO_CBS, 'N'),
      COALESCE(C.RED_IBS, 0), COALESCE(C.RED_CBS, 0)
    FROM CFOP C
    WHERE C.CFOP_INT = NEW.CFOP_PRODUTO
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF (((V_CST = '') OR (V_CCLAS = '')) AND (NEW.CODIGO_PRODUTO IS NOT NULL)) THEN
  BEGIN
    SELECT
      COALESCE(P.CST_IBSCBS, ''), COALESCE(P.CCLASSTRIB, ''),
      COALESCE(P.ALIQ_IBS, 0), COALESCE(P.ALIQ_CBS, 0), COALESCE(P.ALIQ_IBS_MUN, 0),
      COALESCE(P.ISENTO_IBS, 'N'), COALESCE(P.ISENTO_CBS, 'N'),
      COALESCE(P.RED_IBS, 0), COALESCE(P.RED_CBS, 0), P.CODIGO_TIPOTRIBUTACAO
    FROM PRODUTO P
    WHERE P.CODIGO_EMPRESA = NEW.CODIGO_EMPRESA AND P.CODIGO_PRODUTO = NEW.CODIGO_PRODUTO
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS, :V_TT;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF (((V_CST = '') OR (V_CCLAS = '')) AND (V_TT IS NOT NULL)) THEN
  BEGIN
    SELECT
      COALESCE(T.CST_IBSCBS, ''), COALESCE(T.CCLASSTRIB, ''),
      COALESCE(T.ALIQ_IBS, 0), COALESCE(T.ALIQ_CBS, 0), COALESCE(T.ALIQ_IBS_MUN, 0),
      COALESCE(T.ISENTO_IBS, 'N'), COALESCE(T.ISENTO_CBS, 'N'),
      COALESCE(T.RED_IBS, 0), COALESCE(T.RED_CBS, 0)
    FROM TIPOTRIBUTACAO T
    WHERE T.CODIGO_EMPRESA = NEW.CODIGO_EMPRESA AND T.CODIGO_TIPOTRIBUTACAO = :V_TT
    INTO :V_CST, :V_CCLAS, :V_AIBS, :V_ACBS, :V_AIBS_MUN, :V_ISIBS, :V_ISCBS, :V_REDIBS, :V_REDCBS;
    V_CST = TRIM(COALESCE(V_CST,''));
    V_CCLAS = TRIM(COALESCE(V_CCLAS,''));
  END

  IF ((V_CST = '') OR (V_CCLAS = '')) THEN EXIT;

  IF (CHAR_LENGTH(V_CST) = 1) THEN V_CST = '00' || V_CST;
  ELSE IF (CHAR_LENGTH(V_CST) = 2) THEN V_CST = '0' || V_CST;

  IF (CHAR_LENGTH(V_CCLAS) = 1) THEN V_CCLAS = '00000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 2) THEN V_CCLAS = '0000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 3) THEN V_CCLAS = '000' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 4) THEN V_CCLAS = '00' || V_CCLAS;
  ELSE IF (CHAR_LENGTH(V_CCLAS) = 5) THEN V_CCLAS = '0' || V_CCLAS;

  V_BASE = COALESCE(NEW.VALORTOTAL_PRODUTO, 0) - COALESCE(NEW.VALORDESC_PRODUTO, 0) + COALESCE(NEW.ACRESCIMO_PRODUTO, 0);
  IF (V_BASE < 0) THEN V_BASE = 0;

  IF (NEW.CST_IBSCBS IS NULL OR TRIM(NEW.CST_IBSCBS) = '') THEN NEW.CST_IBSCBS = V_CST;
  IF (NEW.CCLASSTRIB IS NULL OR TRIM(NEW.CCLASSTRIB) = '') THEN NEW.CCLASSTRIB = V_CCLAS;
  IF (NEW.ISENTO_IBS IS NULL OR TRIM(NEW.ISENTO_IBS) = '') THEN NEW.ISENTO_IBS = COALESCE(V_ISIBS,'N');
  IF (NEW.ISENTO_CBS IS NULL OR TRIM(NEW.ISENTO_CBS) = '') THEN NEW.ISENTO_CBS = COALESCE(V_ISCBS,'N');
  IF (NEW.RED_IBS IS NULL) THEN NEW.RED_IBS = COALESCE(V_REDIBS,0);
  IF (NEW.RED_CBS IS NULL) THEN NEW.RED_CBS = COALESCE(V_REDCBS,0);
  IF (NEW.ALIQ_IBS IS NULL) THEN NEW.ALIQ_IBS = COALESCE(V_AIBS,0);
  IF (NEW.ALIQ_IBS_MUN IS NULL) THEN NEW.ALIQ_IBS_MUN = COALESCE(V_AIBS_MUN,0);
  IF (NEW.ALIQ_CBS IS NULL) THEN NEW.ALIQ_CBS = COALESCE(V_ACBS,0);

  NEW.BASE_IBS = V_BASE;
  NEW.BASE_CBS = V_BASE;

  IF (NEW.ISENTO_IBS = 'S') THEN
  BEGIN
    NEW.VLR_IBS = 0; NEW.BASE_IBS = 0;
  END    
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_IBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_IBS,0) * V_REDFACTOR;
    NEW.VLR_IBS = (V_BASE * V_ALIQEFET) / 100;
  END

  IF (NEW.ISENTO_IBS = 'S') THEN NEW.VLR_IBS_MUN = 0;
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_IBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_IBS_MUN,0) * V_REDFACTOR;
    NEW.VLR_IBS_MUN = (V_BASE * V_ALIQEFET) / 100;
  END    

  IF (NEW.ISENTO_CBS = 'S') THEN
  BEGIN
    NEW.VLR_CBS = 0; NEW.BASE_CBS = 0;
  END    
  ELSE
  BEGIN
    V_REDFACTOR = 1 - (COALESCE(NEW.RED_CBS,0) / 100);
    V_ALIQEFET  = COALESCE(NEW.ALIQ_CBS,0) * V_REDFACTOR;
    NEW.VLR_CBS = (V_BASE * V_ALIQEFET) / 100;
  END
END ^