SET TERM ^ ;

CREATE OR ALTER procedure SPU_NFS_DIFAL (
    NFS_ID integer)
as
declare variable IPICMSINTERPART numeric(15,2);
declare variable IND_FINAL char(1);
declare variable ORIGEM_UF varchar(2);
declare variable DESTINO_UF varchar(2);
declare variable EMPRESA_IE varchar(20);
declare variable REGIME_TRIBUTARIO char(1);
declare variable TIPO CHAR(1);
declare variable DATA_EMISSAO date;
declare variable NFS_ITEM_ID integer;
declare variable PRODUTO_ID integer;
declare variable VALOR_BASECALCULO_ICMS numeric(15,2);
declare variable PERCENTUAL_ICMS numeric(15,2);
declare variable ALIQUOTA_ICMS_DESTINO numeric(15,2);
declare variable ALIQUOTA_ICMS_ORIGEM numeric(15,2);
declare variable ALIQUOTA_FCP numeric(15,2);
declare variable ALIQUOTA_INTERESTADUAL numeric(15,2);
declare variable IVBCUFDEST numeric(15,2);
declare variable IPFCPUFDEST numeric(15,2);
declare variable IPICMSUFDEST numeric(15,2);
declare variable IPICMSINTER numeric(15,2);
declare variable IVFCPUFDEST numeric(15,2);
declare variable IVICMSUFDEST numeric(15,2);
declare variable IVICMSUFREMET numeric(15,2);

BEGIN

  FOR
  SELECT
    N.IND_FINAL,
    E.UF ORIGEM_UF,
    N.EMPRESA_UF DESTINO_UF,
    N.EMPRESA_IE,
    E.REGIME_TRIBUTARIO,
    N.DATA_EMISSAO,
    N.TIPO
  FROM TBNFS N, TBESTABELECIMENTO E
  WHERE N.ESTABELECIMENTO_CODIGO = E.CODIGO
    AND CONTROLE = :NFS_ID
  INTO :IND_FINAL, :ORIGEM_UF, :DESTINO_UF, :EMPRESA_IE, :REGIME_TRIBUTARIO, :DATA_EMISSAO, :TIPO
    DO
      BEGIN

          UPDATE TBNFS_ITEM SET
              VBCUFDEST    = 0,
              PFCPUFDEST   = 0,
              PICMSUFDEST  = 0,
              PICMSINTER   = 0,
              VFCPUFDEST   = 0,
              VICMSUFDEST  = 0,
              VICMSUFREMET = 0,
              PICMSINTERPART=0
           WHERE NFS_CONTROLE = :NFS_ID;

        IF(:TIPO <> '2' AND :TIPO <> '3' AND :REGIME_TRIBUTARIO <> '1') THEN
        BEGIN

            IF (IND_FINAL = '1' AND ORIGEM_UF <> DESTINO_UF AND DESTINO_UF <> 'EX' AND TRIM(EMPRESA_IE) = '') THEN
                BEGIN
    
                  -- PERCENTUAL REFERENTE AO ANO DE 2016 (40%)
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2015) THEN BEGIN IPICMSINTERPART = 20.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2016) THEN BEGIN IPICMSINTERPART = 40.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2017) THEN BEGIN IPICMSINTERPART = 60.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2018) THEN BEGIN IPICMSINTERPART = 80.00; END ELSE
                  IF (DATA_EMISSAO >= '01.01.2019') THEN BEGIN IPICMSINTERPART = 100.00; END
    
                  FOR
                  SELECT
                        T.CONTROLE,
                        T.PRODUTO_CODIGO,
                        IIF(T.VALOR_BASECALCULO_ICMS > 0, T.VALOR_BASECALCULO_ICMS, T.VALOR_CONTABIL),
                        T.PERCENTUAL_ICMS
                    FROM TBNFS_ITEM T
                    INNER JOIN TBCFOP C ON C.CODIGO = T.CFOP_CODIGO
                    INNER JOIN TBTRIBUTACAO_ICMS ICMS ON ICMS.CODIGO = T.TRIBUTACAO_ICMS
                    WHERE NFS_CONTROLE = :NFS_ID
                    AND ICMS.GERA_DIFAL = 1
                    AND C.GERA_DIFAL = 1
                    INTO :NFS_ITEM_ID, :PRODUTO_ID, :VALOR_BASECALCULO_ICMS, :PERCENTUAL_ICMS
                      DO
                        BEGIN
    
                        IF (VALOR_BASECALCULO_ICMS > 0) THEN
                            BEGIN
                    
                               FOR
                                SELECT FIRST 1 COALESCE(U.ALIQUOTA_ICMS,0.00) ALIQUOTA_ICMS_DESTINO,
                                  COALESCE((SELECT FIRST 1 UO.ALIQUOTA_ICMS FROM TBUF UO WHERE UO.UF = :ORIGEM_UF),0.00) ALIQUOTA_ICMS_ORIGEM,
                                  COALESCE((SELECT FIRST 1 FF.ALIQUOTA_FCP FROM TBFAMILIA_FCP FF, TBPRODUTO P
                                             WHERE P.FAMILIA_CODIGO = FF.FAMILIA_ID AND FF.UF = U.UF AND P.CODIGO = :PRODUTO_ID),0.00) ALIQUOTA_FCP,
                                  COALESCE((SELECT FIRST 1 TII.ALIQUOTA_ICMS FROM TBTRIBUTACAO_ICMS_INTERESTADUAL TII
                                             WHERE TII.UF_ORIGEM = :ORIGEM_UF AND TII.UF_DESTINO = :DESTINO_UF),0.00) ALIQUOTA_INTERESTADUAL
                                FROM TBUF U
                                WHERE U.UF = :DESTINO_UF
                                INTO  :ALIQUOTA_ICMS_DESTINO, :ALIQUOTA_ICMS_ORIGEM, :ALIQUOTA_FCP, :ALIQUOTA_INTERESTADUAL
                                    DO
                                      BEGIN
    
                                      IF (REGIME_TRIBUTARIO = '3') THEN -- REGIME NORMAL
                                          BEGIN
    
                                              IVBCUFDEST   = VALOR_BASECALCULO_ICMS;     --BASE DE CÁLCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
                                
                                              IVFCPUFDEST   = VALOR_BASECALCULO_ICMS * (ALIQUOTA_FCP / 100.000);
                                              IVICMSUFDEST  = ROUND(ROUND((VALOR_BASECALCULO_ICMS * ( (ALIQUOTA_ICMS_DESTINO - PERCENTUAL_ICMS) / 100.000)),2) * (IPICMSINTERPART / 100.000),2) ;
                                              IVICMSUFREMET = ROUND(ROUND((VALOR_BASECALCULO_ICMS * ( (ALIQUOTA_ICMS_DESTINO - PERCENTUAL_ICMS) / 100.000)),2) * ((100 - IPICMSINTERPART) / 100.000),2) ;
    
                                          END ELSE
                                      IF (REGIME_TRIBUTARIO <> '3') THEN -- REGIME SIMPLES
                                          BEGIN
    
                                              IVBCUFDEST   = 0;                          --BASE DE CÁLCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
                                
                                              IVFCPUFDEST   = 0;
                                              IVICMSUFDEST  = 0;
                                              IVICMSUFREMET = 0;
    
                                          END
    
                                          UPDATE TBNFS_ITEM SET
                                              VBCUFDEST    = :IVBCUFDEST,
                                              PFCPUFDEST   = :IPFCPUFDEST,
                                              PICMSUFDEST  = :IPICMSUFDEST,
                                              PICMSINTER   = :IPICMSINTER,
                                              VFCPUFDEST   = :IVFCPUFDEST,
                                              VICMSUFDEST  = :IVICMSUFDEST,
                                              VICMSUFREMET = :IVICMSUFREMET,
                                              PICMSINTERPART=:IPICMSINTERPART
                                           WHERE NFS_CONTROLE = :NFS_ID
                                             AND CONTROLE = :NFS_ITEM_ID;
    
                                      END
    
                            END
    
                        END
    
                END
            END

      END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPU_NFS_DIFAL_V2 (
    NFS_ID INTEGER)
AS
DECLARE VARIABLE IPICMSINTERPART NUMERIC(15,2);
DECLARE VARIABLE IND_FINAL CHAR(1);
DECLARE VARIABLE ORIGEM_UF VARCHAR(2);
DECLARE VARIABLE DESTINO_UF VARCHAR(2);
DECLARE VARIABLE EMPRESA_IE VARCHAR(20);
DECLARE VARIABLE REGIME_TRIBUTARIO CHAR(1);
declare variable TIPO CHAR(1);
DECLARE VARIABLE DATA_EMISSAO DATE;
DECLARE VARIABLE NFS_ITEM_ID INTEGER;
DECLARE VARIABLE PRODUTO_ID INTEGER;
DECLARE VARIABLE VALOR_BASECALCULO_ICMS NUMERIC(15,2);
DECLARE VARIABLE PERCENTUAL_ICMS NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_ICMS_DESTINO NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_ICMS_ORIGEM NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_FCP NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_INTERESTADUAL NUMERIC(15,2);
DECLARE VARIABLE IVBCUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPFCPUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPICMSUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPICMSINTER NUMERIC(15,2);
DECLARE VARIABLE IVFCPUFDEST NUMERIC(15,2);
DECLARE VARIABLE IVICMSUFDEST NUMERIC(15,2);
DECLARE VARIABLE IVICMSUFREMET NUMERIC(15,2);
BEGIN

  FOR
  SELECT
        N.IND_FINAL,
        E.UF ORIGEM_UF,
        N.EMPRESA_UF DESTINO_UF,
        N.EMPRESA_IE,
        E.REGIME_TRIBUTARIO,
        N.DATA_EMISSAO,
        N.TIPO
  FROM TBNFS N, TBESTABELECIMENTO E
  WHERE N.ESTABELECIMENTO_CODIGO = E.CODIGO
    AND CONTROLE = :NFS_ID
  INTO :IND_FINAL, :ORIGEM_UF, :DESTINO_UF, :EMPRESA_IE, :REGIME_TRIBUTARIO, :DATA_EMISSAO, :TIPO
    DO
      BEGIN

          UPDATE TBNFS_ITEM SET
              VBCUFDEST    = 0,
              PFCPUFDEST   = 0,
              PICMSUFDEST  = 0,
              PICMSINTER   = 0,
              VFCPUFDEST   = 0,
              VICMSUFDEST  = 0,
              VICMSUFREMET = 0,
              PICMSINTERPART=0
           WHERE NFS_CONTROLE = :NFS_ID;

        IF(:TIPO <> '2' AND :TIPO <> '3' AND :REGIME_TRIBUTARIO <> '1') THEN
        BEGIN
            IF (IND_FINAL = '1' AND ORIGEM_UF <> DESTINO_UF AND DESTINO_UF <> 'EX' AND TRIM(EMPRESA_IE) = '') THEN
                BEGIN
    
                  -- PERCENTUAL REFERENTE AO ANO DE 2016 (40%)
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2015) THEN BEGIN IPICMSINTERPART = 20.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2016) THEN BEGIN IPICMSINTERPART = 40.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2017) THEN BEGIN IPICMSINTERPART = 60.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2018) THEN BEGIN IPICMSINTERPART = 80.00; END ELSE
                  IF (DATA_EMISSAO >= '01.01.2019') THEN BEGIN IPICMSINTERPART = 100.00; END
    
                  FOR
                  SELECT
                        T.CONTROLE,
                        T.PRODUTO_CODIGO,
                        IIF(T.VALOR_BASECALCULO_ICMS > 0, T.VALOR_BASECALCULO_ICMS, T.VALOR_CONTABIL),
                        T.PERCENTUAL_ICMS
                    FROM TBNFS_ITEM T
                    INNER JOIN TBCFOP C ON C.CODIGO = T.CFOP_CODIGO
                    INNER JOIN TBTRIBUTACAO_ICMS ICMS ON ICMS.CODIGO = T.TRIBUTACAO_ICMS
                    WHERE NFS_CONTROLE = :NFS_ID
                    AND ICMS.GERA_DIFAL = 1
                    AND C.GERA_DIFAL = 1
                    INTO :NFS_ITEM_ID, :PRODUTO_ID, :VALOR_BASECALCULO_ICMS, :PERCENTUAL_ICMS
                      DO
                        BEGIN
    
                        IF (VALOR_BASECALCULO_ICMS > 0) THEN
                            BEGIN
                    
                               FOR
                                SELECT FIRST 1 COALESCE(U.ALIQUOTA_ICMS,0.00) ALIQUOTA_ICMS_DESTINO,
                                  COALESCE((SELECT FIRST 1 UO.ALIQUOTA_ICMS FROM TBUF UO WHERE UO.UF = :ORIGEM_UF),0.00) ALIQUOTA_ICMS_ORIGEM,
                                  COALESCE((SELECT FIRST 1 FF.ALIQUOTA_FCP FROM TBFAMILIA_FCP FF, TBPRODUTO P
                                             WHERE P.FAMILIA_CODIGO = FF.FAMILIA_ID AND FF.UF = U.UF AND P.CODIGO = :PRODUTO_ID),0.00) ALIQUOTA_FCP,
                                  COALESCE((SELECT FIRST 1 TII.ALIQUOTA_ICMS FROM TBTRIBUTACAO_ICMS_INTERESTADUAL TII
                                             WHERE TII.UF_ORIGEM = :ORIGEM_UF AND TII.UF_DESTINO = :DESTINO_UF),0.00) ALIQUOTA_INTERESTADUAL
    
                                FROM TBUF U
                                WHERE U.UF = :DESTINO_UF
                                INTO  :ALIQUOTA_ICMS_DESTINO, :ALIQUOTA_ICMS_ORIGEM, :ALIQUOTA_FCP, :ALIQUOTA_INTERESTADUAL
                                    DO
                                      BEGIN
    
                                      IF (REGIME_TRIBUTARIO = '3') THEN -- REGIME NORMAL
                                          BEGIN
    
                                              IVBCUFDEST   = VALOR_BASECALCULO_ICMS;     --BASE DE CÁLCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IF (PERCENTUAL_ICMS = 4) THEN
                                                  IPICMSINTER  = 4;
                                                  ELSE
                                                  IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
                                
                                              IVFCPUFDEST   = VALOR_BASECALCULO_ICMS * (ALIQUOTA_FCP / 100.000);
                                              IVICMSUFDEST  = ROUND(ROUND((VALOR_BASECALCULO_ICMS * ( (ALIQUOTA_ICMS_DESTINO - PERCENTUAL_ICMS) / 100.000)),2) * (IPICMSINTERPART / 100.000),2) ;
                                              IVICMSUFREMET = ROUND(ROUND((VALOR_BASECALCULO_ICMS * ( (ALIQUOTA_ICMS_DESTINO - PERCENTUAL_ICMS) / 100.000)),2) * ((100 - IPICMSINTERPART) / 100.000),2) ;
    
                                          END ELSE
                                      IF (REGIME_TRIBUTARIO <> '3') THEN -- REGIME SIMPLES
                                          BEGIN
    
                                              IVBCUFDEST   = 0;                          --BASE DE CÁLCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IF (PERCENTUAL_ICMS = 4) THEN
                                                  IPICMSINTER  = 4;
                                                  ELSE
                                                  IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
    
                                              IVFCPUFDEST   = 0;
                                              IVICMSUFDEST  = 0;
                                              IVICMSUFREMET = 0;
    
                                          END
    
                                          UPDATE TBNFS_ITEM SET
                                              VBCUFDEST    = :IVBCUFDEST,
                                              PFCPUFDEST   = :IPFCPUFDEST,
                                              PICMSUFDEST  = :IPICMSUFDEST,
                                              PICMSINTER   = :IPICMSINTER,
                                              VFCPUFDEST   = :IVFCPUFDEST,
                                              VICMSUFDEST  = :IVICMSUFDEST,
                                              VICMSUFREMET = :IVICMSUFREMET,
                                              PICMSINTERPART=:IPICMSINTERPART
                                           WHERE NFS_CONTROLE = :NFS_ID
                                             AND CONTROLE = :NFS_ITEM_ID;
    
                                      END
    
                            END
    
                        END
    
                END
          END

      END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPU_NFS_DIFAL_V3 (
    NFS_ID INTEGER)
AS
DECLARE VARIABLE IPICMSINTERPART NUMERIC(15,2);
DECLARE VARIABLE IND_FINAL CHAR(1);
DECLARE VARIABLE ORIGEM_UF VARCHAR(2);
DECLARE VARIABLE DESTINO_UF VARCHAR(2);
DECLARE VARIABLE EMPRESA_IE VARCHAR(20);
DECLARE VARIABLE REGIME_TRIBUTARIO CHAR(1);
declare variable TIPO CHAR(1);
DECLARE VARIABLE DATA_EMISSAO DATE;
DECLARE VARIABLE NFS_ITEM_ID INTEGER;
DECLARE VARIABLE PRODUTO_ID INTEGER;
DECLARE VARIABLE NCM_CODIGO VARCHAR(10);
DECLARE VARIABLE VALOR_BASECALCULO_ICMS NUMERIC(15,2);
DECLARE VARIABLE PERCENTUAL_ICMS NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_ICMS_DESTINO NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_ICMS_ORIGEM NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_FCP NUMERIC(15,2);
DECLARE VARIABLE ALIQUOTA_INTERESTADUAL NUMERIC(15,2);
DECLARE VARIABLE IVBCUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPFCPUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPICMSUFDEST NUMERIC(15,2);
DECLARE VARIABLE IPICMSINTER NUMERIC(15,2);
DECLARE VARIABLE IVFCPUFDEST NUMERIC(15,2);
DECLARE VARIABLE IVICMSUFDEST NUMERIC(15,2);
DECLARE VARIABLE IVICMSUFREMET NUMERIC(15,2);
DECLARE VARIABLE FLAG_DIFAL INTEGER;
DECLARE VARIABLE DISPARAR_EMAIL INTEGER;
DECLARE VARIABLE ASSUNTO_EMAIL VARCHAR(500);
DECLARE VARIABLE TITULO_EMAIL VARCHAR(500);
DECLARE VARIABLE CORPO1 BLOB SUB_TYPE 1 SEGMENT SIZE 80;
DECLARE VARIABLE ESTAB_NOMEFANTASIA VARCHAR(200);
DECLARE VARIABLE CLIENTE_RAZAOSOCIAL VARCHAR(300);
DECLARE VARIABLE DOCUMENTO_SERIE VARCHAR(100);
DECLARE VARIABLE VALOR_TOTAL NUMERIC(15,2);
DECLARE VARIABLE CLIENTE_CNPJ VARCHAR(100);
DECLARE VARIABLE VALOR_TOTAL_DIFAL NUMERIC(15,2);
DECLARE VARIABLE EMAIL VARCHAR(200);
DECLARE VARIABLE USUARIO_ID INTEGER;
BEGIN

  :DISPARAR_EMAIL = 1;

  FOR
  SELECT
        N.IND_FINAL,
        E.UF ORIGEM_UF,
        N.EMPRESA_UF DESTINO_UF,
        N.EMPRESA_IE,
        E.REGIME_TRIBUTARIO,
        N.DATA_EMISSAO,
        E.NOMEFANTASIA AS ESTAB_NOMEFANTASIA,
        COALESCE((SELECT FIRST 1 C.RAZAOSOCIAL||'('||C.UF||')' FROM TBEMPRESA C WHERE C.CODIGO = N.EMPRESA_CODIGO),'') AS CLIENTE_RAZAOSOCIAL,
        COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(C.CNPJ) > 11,' CNPJ:'||FN_MASK(C.CNPJ,'CNPJ'),' CPF:'||FN_MASK(C.CNPJ,'CPF')) FROM TBEMPRESA C WHERE C.CODIGO = N.EMPRESA_CODIGO),'') AS CLIENTE_CNPJ,
        FN_LPAD(N.NUMERO_NOTAFISCAL,9,0)||'/'||FN_LPAD(N.SERIE,3,0) AS DOCUMENTO_SERIE,
        CAST(COALESCE((SELECT FIRST 1 T.VALOR_TOTAL_NF FROM TBNFS_TOTAIS T WHERE T.NFS_CONTROLE = N.CONTROLE), 0) AS NUMERIC(15,2)) AS VALOR_TOTAL,
        N.TIPO
  FROM TBNFS N, TBESTABELECIMENTO E
  WHERE N.ESTABELECIMENTO_CODIGO = E.CODIGO
    AND CONTROLE = :NFS_ID
  INTO :IND_FINAL, :ORIGEM_UF, :DESTINO_UF, :EMPRESA_IE, :REGIME_TRIBUTARIO, :DATA_EMISSAO, :ESTAB_NOMEFANTASIA, :CLIENTE_RAZAOSOCIAL, :CLIENTE_CNPJ, :DOCUMENTO_SERIE, :VALOR_TOTAL, :TIPO
    DO
      BEGIN

          UPDATE TBNFS_ITEM SET
              VBCUFDEST    = 0,
              PFCPUFDEST   = 0,
              PICMSUFDEST  = 0,
              PICMSINTER   = 0,
              VFCPUFDEST   = 0,
              VICMSUFDEST  = 0,
              VICMSUFREMET = 0,
              PICMSINTERPART=0
           WHERE NFS_CONTROLE = :NFS_ID;

        IF(:TIPO <> '2' AND :TIPO <> '3' AND :REGIME_TRIBUTARIO <> '1') THEN
        BEGIN
            IF ( (ORIGEM_UF <> DESTINO_UF AND DESTINO_UF <> 'EX') AND (TRIM(EMPRESA_IE) = '') ) THEN
                BEGIN
    
                  -- PERCENTUAL REFERENTE AO ANO DE 2016 (40%)
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2015) THEN BEGIN IPICMSINTERPART = 20.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2016) THEN BEGIN IPICMSINTERPART = 40.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2017) THEN BEGIN IPICMSINTERPART = 60.00; END ELSE
                  IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2018) THEN BEGIN IPICMSINTERPART = 80.00; END ELSE
                  IF (DATA_EMISSAO >= '01.01.2019') THEN BEGIN IPICMSINTERPART = 100.00; END
    
                  FOR
                    SELECT
                        T.CONTROLE,
                        T.PRODUTO_CODIGO,
                        IIF(T.VALOR_BASECALCULO_ICMS > 0, T.VALOR_BASECALCULO_ICMS, T.VALOR_CONTABIL),
                        T.PERCENTUAL_ICMS,
                        T.NCM_CODIGO
                    FROM TBNFS_ITEM T
                    INNER JOIN TBCFOP C ON C.CODIGO = T.CFOP_CODIGO
                    INNER JOIN TBTRIBUTACAO_ICMS ICMS ON ICMS.CODIGO = T.TRIBUTACAO_ICMS
                    WHERE NFS_CONTROLE = :NFS_ID
                    AND ICMS.GERA_DIFAL = 1
                    AND C.GERA_DIFAL = 1
                    INTO :NFS_ITEM_ID, :PRODUTO_ID, :VALOR_BASECALCULO_ICMS, :PERCENTUAL_ICMS, :NCM_CODIGO
                      DO
                        BEGIN
    
                        IF (VALOR_BASECALCULO_ICMS > 0) THEN
                            BEGIN
                    
                               FOR
                                SELECT FIRST 1 COALESCE(U.ALIQUOTA_ICMS,0.00) ALIQUOTA_ICMS_DESTINO,
                                  COALESCE((SELECT FIRST 1 UO.ALIQUOTA_ICMS FROM TBUF UO WHERE UO.UF = :ORIGEM_UF),0.00) ALIQUOTA_ICMS_ORIGEM,
                                  COALESCE((SELECT FIRST 1 FF.ALIQUOTA_FCP FROM TBFAMILIA_FCP FF, TBPRODUTO P
                                             WHERE P.FAMILIA_CODIGO = FF.FAMILIA_ID AND FF.UF = U.UF AND FF.NCM_CODIGO = :NCM_CODIGO AND P.CODIGO = :PRODUTO_ID),0.00) ALIQUOTA_FCP,
                                  COALESCE((SELECT FIRST 1 TII.ALIQUOTA_ICMS FROM TBTRIBUTACAO_ICMS_INTERESTADUAL TII
                                             WHERE TII.UF_ORIGEM = :ORIGEM_UF AND TII.UF_DESTINO = :DESTINO_UF),0.00) ALIQUOTA_INTERESTADUAL
    
                                FROM TBUF U
                                WHERE U.UF = :DESTINO_UF
                                INTO  :ALIQUOTA_ICMS_DESTINO, :ALIQUOTA_ICMS_ORIGEM, :ALIQUOTA_FCP, :ALIQUOTA_INTERESTADUAL
                                    DO
                                      BEGIN
    
                                      IF (REGIME_TRIBUTARIO = '3') THEN -- REGIME NORMAL
                                          BEGIN
    
                                              IVBCUFDEST   = VALOR_BASECALCULO_ICMS;     --BASE DE C??LCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IF (PERCENTUAL_ICMS = 4) THEN
                                                  IPICMSINTER  = 4;
                                                  ELSE
                                                  IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
                                
                                              IVFCPUFDEST   = CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * (CAST(ALIQUOTA_FCP AS DOUBLE PRECISION) / 100.000);
                                              IVICMSUFDEST  = ROUND(ROUND((CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * ( (CAST(ALIQUOTA_ICMS_DESTINO AS DOUBLE PRECISION) - CAST(PERCENTUAL_ICMS AS DOUBLE PRECISION)) / 100.000)),2) * (CAST(IPICMSINTERPART AS DOUBLE PRECISION) / 100.000),2) ;
                                              IVICMSUFREMET = ROUND(ROUND((CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * ( (CAST(ALIQUOTA_ICMS_DESTINO AS DOUBLE PRECISION) - CAST(PERCENTUAL_ICMS AS DOUBLE PRECISION)) / 100.000)),2) * ((100 - CAST(IPICMSINTERPART AS DOUBLE PRECISION)) / 100.000),2) ;
    
                                          END ELSE
                                      IF (REGIME_TRIBUTARIO <> '3') THEN -- REGIME SIMPLES
                                          BEGIN
    
                                              IVBCUFDEST   = 0;                          --BASE DE C??LCULO
                                              IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                              IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                              IF (PERCENTUAL_ICMS = 4) THEN
                                                  IPICMSINTER  = 4;
                                                  ELSE
                                                  IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
    
                                              IVFCPUFDEST   = 0;
                                              IVICMSUFDEST  = 0;
                                              IVICMSUFREMET = 0;
    
                                          END
    
                                          :FLAG_DIFAL = CAST(FN_PARAMETRO('SISTEMA.FLAG_DIFAL') AS INTEGER);
                                          IF(:FLAG_DIFAL = 1) THEN
                                          BEGIN
    
                                                UPDATE TBNFS_ITEM SET
                                                  VBCUFDEST    = :IVBCUFDEST,
                                                  PFCPUFDEST   = :IPFCPUFDEST,
                                                  PICMSUFDEST  = :IPICMSUFDEST,
                                                  PICMSINTER   = :IPICMSINTER,
                                                  VFCPUFDEST   = :IVFCPUFDEST,
                                                  VICMSUFDEST  = :IVICMSUFDEST,
                                                  VICMSUFREMET = :IVICMSUFREMET,
                                                  PICMSINTERPART=:IPICMSINTERPART
                                                WHERE NFS_CONTROLE = :NFS_ID
                                                 AND CONTROLE = :NFS_ITEM_ID;
    
                                                :DISPARAR_EMAIL = 0;
    
                                          END ELSE
                                          BEGIN
    
                                                UPDATE TBNFS_ITEM SET
                                                   VFCPUFDEST   = :IVFCPUFDEST
                                                WHERE NFS_CONTROLE = :NFS_ID
                                                 AND CONTROLE = :NFS_ITEM_ID;
    
                                          END
    
    
                                      END
    
                            END
    
                        END
    
                        -- DIFAL DA UF DE ORIGEM - VICMSUFDEST
                        -- FCP - VFCPUFDEST
                        -- DIFAL DA UF DESTINO - VICMSUFREMET
                        IF(:DISPARAR_EMAIL = 0)THEN
                        BEGIN
                            :VALOR_TOTAL_DIFAL = CAST(COALESCE((SELECT SUM(COALESCE(A.VICMSUFDEST,0)+COALESCE(A.VFCPUFDEST,0)+COALESCE(A.VICMSUFREMET,0)) FROM TBNFS_ITEM A WHERE A.NFS_CONTROLE = :NFS_ID),0) AS NUMERIC(15,2));
                            :DISPARAR_EMAIL    = 1;
                            :ASSUNTO_EMAIL     = 'DOCUMENTO FISCAL '||:DOCUMENTO_SERIE||' COM DIFAL EMITIDA';
                            :TITULO_EMAIL      = 'DOCUMENTO FISCAL '||:DOCUMENTO_SERIE||' COM DIFAL EMITIDA';
    
                            CORPO1 = '<HTML><HEAD><META CHARSET="CONTENT-TYPE: TEXT/HTML; CHARSET=UTF-8"><STYLE>P {MARGIN: 2PX;}</STYLE></HEAD><BODY>';
                            CORPO1 = CORPO1 || '<P></P>';
                            CORPO1 = CORPO1 || '<P><A HREF="'||COALESCE(FN_PARAMETRO('SISTEMA.URL_SERVER')||'/_21150?NF_ID='||:NFS_ID,'')||'" TARGET="_BLANK">[ABRIR NA WEB]</A></P>';
                        --    CORPO1 = CORPO1 || '<P><A HREF="'||COALESCE(FN_PARAMETRO('SISTEMA.URL_SERVER')||'/_12360?CLIENTE_ID='||:CLIENTE_CODIGO,'')||'" TARGET="_BLANK">[ABRIR CLIENTE NA WEB]</A></P>';
                            CORPO1 = CORPO1 || '<BR>';
                            CORPO1 = CORPO1 || '<P><U>DOCUMENTO FISCAL: '||:DOCUMENTO_SERIE||'</U></P>';
                            CORPO1 = CORPO1 || '<P><U>DATA DE EMISSAO</U>: '||FN_DATE(:DATA_EMISSAO, 'DD/MM/YYYY')||'</P>';
                            CORPO1 = CORPO1 || '<P><U>ESTABELECIMENTO</U>: '||COALESCE(:ESTAB_NOMEFANTASIA,'')||'</P>';
                            CORPO1 = CORPO1 || '<P><U>DESTINATARIO</U>: '||COALESCE(:CLIENTE_RAZAOSOCIAL,'')||'</P>';
                            CORPO1 = CORPO1 || '<P><U>'||COALESCE(:CLIENTE_CNPJ,'')||'</U></P>';
                            CORPO1 = CORPO1 || '<P><U>VALOR TOTAL</U>: R$ '||:VALOR_TOTAL||'</P>';
                            CORPO1 = CORPO1 || '<P><U>VALOR DIFAL</U>: R$ '||:VALOR_TOTAL_DIFAL||'</P>';
                        
                            CORPO1 = CORPO1 || '</TBODY></TABLE>';
                            /*SE HOUVER MOTIVO DEVE SER ADICIONADO NO EMAIL AP??S A TABELA E SEU RESUMO*/
                        
                            CORPO1 = CORPO1 || '</BODY></HTML>';
        
                            FOR
                              SELECT EMAIL, CODIGO
                               FROM(
                                SELECT
                                    U.EMAIL,
                                    U.CODIGO
                                    FROM TBUSUARIO U, (SELECT OSPLIT AS USUARIO_ID FROM SPLIT_V2(FN_PARAMETRO('SISTEMA.USUARIOS_NOTIFICAR_DIFAL',FN_CURRENT_USER_ID()),',')) US
                                    WHERE U.CODIGO = US.USUARIO_ID
                                    AND CHAR_LENGTH(COALESCE(U.EMAIL,'')) > 0
                                )
                                INTO
                                    :EMAIL,
                                    :USUARIO_ID
                                DO BEGIN
                                    IF (CHAR_LENGTH(COALESCE(:EMAIL,'')) > 0) THEN
                                    BEGIN
                                       EXECUTE PROCEDURE SPI_NOTIFICAR_V2(
                                          CURRENT_TIMESTAMP,
                                          :USUARIO_ID,
                                          :ASSUNTO_EMAIL,
                                          :CORPO1,
                                          1,
                                          0
                                       );
                                    END
                                END
        
                        END
    
                END
          END
      END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPU_DIFAL (
    NFS_ID integer)
as
declare variable IPICMSINTERPART numeric(15,2);
declare variable IND_FINAL char(1);
declare variable ORIGEM_UF varchar(2);
declare variable DESTINO_UF varchar(2);
declare variable EMPRESA_IE varchar(20);
declare variable REGIME_TRIBUTARIO char(1);
declare variable DATA_EMISSAO date;
declare variable NFS_ITEM_ID integer;
declare variable PRODUTO_ID integer;
declare variable FAMILIA_ID integer;
declare variable NCM_CODIGO varchar(10);
declare variable VALOR_BASECALCULO_ICMS numeric(15,2);
declare variable PERCENTUAL_ICMS numeric(15,2);
declare variable ALIQUOTA_ICMS_DESTINO numeric(15,2);
declare variable ALIQUOTA_ICMS_ORIGEM numeric(15,2);
declare variable ALIQUOTA_FCP numeric(15,2);
declare variable ALIQUOTA_INTERESTADUAL numeric(15,2);
declare variable IVBCUFDEST numeric(15,2);
declare variable IPFCPUFDEST numeric(15,2);
declare variable IPICMSUFDEST numeric(15,2);
declare variable IPICMSINTER numeric(15,2);
declare variable IVFCPUFDEST numeric(15,2);
declare variable IVICMSUFDEST numeric(15,2);
declare variable IVICMSUFREMET numeric(15,2);
declare variable FLAG_DIFAL integer;
declare variable ASSUNTO_EMAIL varchar(500);
declare variable TITULO_EMAIL varchar(500);
declare variable CORPO1 blob sub_type 1 segment size 80;
declare variable ESTAB_NOMEFANTASIA varchar(200);
declare variable CLIENTE_RAZAOSOCIAL varchar(300);
declare variable VALOR_TOTAL numeric(15,2);
declare variable CLIENTE_CNPJ varchar(100);
declare variable VALOR_TOTAL_DIFAL numeric(15,2);
declare variable EMAIL varchar(200);
declare variable USUARIO_ID integer;
BEGIN

  FOR SELECT
        N.IND_FINAL,
        E.UF ORIGEM_UF,
        N.CLIENTE_UF DESTINO_UF,
        N.CLIENTE_IE,
        E.REGIME_TRIBUTARIO,
        N.DATA_EMISSAO,
        E.NOMEFANTASIA AS ESTAB_NOMEFANTASIA,
        N.CLIENTE_NOME,
        N.CLIENTE_CPF,
        (SELECT SUM(X.VALOR_LIQUIDO) FROM TBPDV_CFE_ITEM X WHERE X.PDV_CFE_ID = N.ID) AS VALOR_TOTAL
  FROM TBPDV_CFE N, TBESTABELECIMENTO E
  WHERE N.ESTABELECIMENTO_ID = E.CODIGO
    AND N.ID = :NFS_ID
  INTO :IND_FINAL, :ORIGEM_UF, :DESTINO_UF, :EMPRESA_IE, :REGIME_TRIBUTARIO, :DATA_EMISSAO, :ESTAB_NOMEFANTASIA, :CLIENTE_RAZAOSOCIAL, :CLIENTE_CNPJ, :VALOR_TOTAL
    DO
      BEGIN

          UPDATE TBPDV_CFE_ITEM SET
              VBCUFDEST    = 0,
              PFCPUFDEST   = 0,
              PICMSUFDEST  = 0,
              PICMSINTER   = 0,
              VFCPUFDEST   = 0,
              VICMSUFDEST  = 0,
              VICMSUFREMET = 0,
              PICMSINTERPART=0
           WHERE PDV_CFE_ID = :NFS_ID;


        IF ( (ORIGEM_UF <> DESTINO_UF AND DESTINO_UF <> 'EX') AND (TRIM(EMPRESA_IE) = '') ) THEN
            BEGIN

              -- PERCENTUAL REFERENTE AO ANO DE 2016 (40%)
              IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2015) THEN BEGIN IPICMSINTERPART = 20.00; END ELSE
              IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2016) THEN BEGIN IPICMSINTERPART = 40.00; END ELSE
              IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2017) THEN BEGIN IPICMSINTERPART = 60.00; END ELSE
              IF (EXTRACT (YEAR FROM DATA_EMISSAO) = 2018) THEN BEGIN IPICMSINTERPART = 80.00; END ELSE
              IF (DATA_EMISSAO >= '01.01.2019') THEN BEGIN IPICMSINTERPART = 100.00; END

              FOR
              SELECT
                    T.ID,
                    T.PRODUTO_ID,
                    IIF(T.BASECALCULO_ICMS > 0, T.BASECALCULO_ICMS, T.VALOR_CONTABIL),
                    T.ALIQUOTA_ICMS,
                    T.NCM_CODIGO,
                    T.FAMILIA_ID
                FROM TBPDV_CFE_ITEM T
                INNER JOIN TBCFOP C ON C.CODIGO = T.CFOP_CODIGO
                INNER JOIN TBTRIBUTACAO_ICMS ICMS ON ICMS.CODIGO = T.TRIBUTACAO_ICMS
                WHERE PDV_CFE_ID = :NFS_ID
                AND ICMS.GERA_DIFAL = 1
                AND C.GERA_DIFAL = 1
                INTO :NFS_ITEM_ID, :PRODUTO_ID, :VALOR_BASECALCULO_ICMS, :PERCENTUAL_ICMS, :NCM_CODIGO, :FAMILIA_ID
                  DO
                    BEGIN

                    IF (VALOR_BASECALCULO_ICMS > 0) THEN
                        BEGIN
                
                           FOR
                            SELECT FIRST 1 COALESCE(U.ALIQUOTA_ICMS,0.00) ALIQUOTA_ICMS_DESTINO,
                              COALESCE((SELECT FIRST 1 UO.ALIQUOTA_ICMS FROM TBUF UO WHERE UO.UF = :ORIGEM_UF),0.00) ALIQUOTA_ICMS_ORIGEM,
                              COALESCE((SELECT FIRST 1 FF.ALIQUOTA_FCP FROM TBFAMILIA_FCP FF
                                         WHERE FF.FAMILIA_ID = :FAMILIA_ID AND FF.UF = U.UF AND FF.NCM_CODIGO = :NCM_CODIGO),0.00) ALIQUOTA_FCP,
                              COALESCE((SELECT FIRST 1 TII.ALIQUOTA_ICMS FROM TBTRIBUTACAO_ICMS_INTERESTADUAL TII
                                         WHERE TII.UF_ORIGEM = :ORIGEM_UF AND TII.UF_DESTINO = :DESTINO_UF),0.00) ALIQUOTA_INTERESTADUAL

                            FROM TBUF U
                            WHERE U.UF = :DESTINO_UF
                            INTO  :ALIQUOTA_ICMS_DESTINO, :ALIQUOTA_ICMS_ORIGEM, :ALIQUOTA_FCP, :ALIQUOTA_INTERESTADUAL
                                DO
                                  BEGIN

                                  IF (REGIME_TRIBUTARIO = '3') THEN -- REGIME NORMAL
                                      BEGIN

                                          IVBCUFDEST   = VALOR_BASECALCULO_ICMS;     --BASE DE C??LCULO
                                          IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                          IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                          IF (PERCENTUAL_ICMS = 4) THEN
                                              IPICMSINTER  = 4;
                                              ELSE
                                              IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL
                            
                                          IVFCPUFDEST   = CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * (CAST(ALIQUOTA_FCP AS DOUBLE PRECISION) / 100.000);
                                          IVICMSUFDEST  = ROUND(ROUND((CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * ( (CAST(ALIQUOTA_ICMS_DESTINO AS DOUBLE PRECISION) - CAST(PERCENTUAL_ICMS AS DOUBLE PRECISION)) / 100.000)),2) * (CAST(IPICMSINTERPART AS DOUBLE PRECISION) / 100.000),2) ;
                                          IVICMSUFREMET = ROUND(ROUND((CAST(VALOR_BASECALCULO_ICMS AS DOUBLE PRECISION) * ( (CAST(ALIQUOTA_ICMS_DESTINO AS DOUBLE PRECISION) - CAST(PERCENTUAL_ICMS AS DOUBLE PRECISION)) / 100.000)),2) * ((100 - CAST(IPICMSINTERPART AS DOUBLE PRECISION)) / 100.000),2) ;

                                      END ELSE
                                  IF (REGIME_TRIBUTARIO <> '3') THEN -- REGIME SIMPLES
                                      BEGIN

                                          IVBCUFDEST   = 0;                          --BASE DE C??LCULO
                                          IPFCPUFDEST  = ALIQUOTA_FCP;               --ALIQUOTA FCP NO DESTINO
                                          IPICMSUFDEST = ALIQUOTA_ICMS_DESTINO;      --ALIQUOTA ICMS INTERNA NO DESTINO
                                          IF (PERCENTUAL_ICMS = 4) THEN
                                              IPICMSINTER  = 4;
                                              ELSE
                                              IPICMSINTER  = ALIQUOTA_INTERESTADUAL;     --ALIQUOTA INTERESTADUAL

                                          IVFCPUFDEST   = 0;
                                          IVICMSUFDEST  = 0;
                                          IVICMSUFREMET = 0;

                                      END

                                      :FLAG_DIFAL = CAST(FN_PARAMETRO('SISTEMA.FLAG_DIFAL') AS INTEGER);
                                      IF(:FLAG_DIFAL = 1) THEN
                                      BEGIN

                                            UPDATE TBPDV_CFE_ITEM SET
                                              VBCUFDEST    = :IVBCUFDEST,
                                              PFCPUFDEST   = :IPFCPUFDEST,
                                              PICMSUFDEST  = :IPICMSUFDEST,
                                              PICMSINTER   = :IPICMSINTER,
                                              VFCPUFDEST   = :IVFCPUFDEST,
                                              VICMSUFDEST  = :IVICMSUFDEST,
                                              VICMSUFREMET = :IVICMSUFREMET,
                                              PICMSINTERPART=:IPICMSINTERPART
                                            WHERE PDV_CFE_ID = :NFS_ID
                                             AND ID = :NFS_ITEM_ID;

                                      END ELSE
                                      BEGIN

                                            UPDATE TBPDV_CFE_ITEM SET
                                               VFCPUFDEST   = :IVFCPUFDEST
                                            WHERE PDV_CFE_ID = :NFS_ID
                                             AND ID = :NFS_ITEM_ID;

                                      END


                                  END

                        END

                    END
            END

      END

END
^

SET TERM ; ^

