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
                    ID,
                    PRODUTO_ID,
                    BASECALCULO_ICMS,
                    ALIQUOTA_ICMS,
                    NCM_CODIGO,
                    FAMILIA_ID
                FROM TBPDV_CFE_ITEM WHERE PDV_CFE_ID = :NFS_ID
                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   = 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

                                      :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 ; ^

