SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPU_BAIXA_DEVOLUCAO_TROCA (
    NFE_ID INTEGER)
AS
DECLARE VARIABLE ESTABELECIMENTO_CODIGO INTEGER;
DECLARE VARIABLE SALDO                  NUMERIC(15,2);
BEGIN

  FOR
     SELECT
         Z.ESTABELECIMENTO_CODIGO,
         Z.SALDO
       FROM(
          SELECT 
            Z.ESTABELECIMENTO_CODIGO,
            CAST(Z.TOTAL_ITEM+Z.TOTAL_OUTROS-COALESCE(Z.TOTAL_USADO,0) AS NUMERIC(15,2)) SALDO
        
            FROM (
                SELECT N.ESTABELECIMENTO_CODIGO, N.CONTROLE, N.NUMERO_NOTAFISCAL, N.SERIE, N.DATA_EMISSAO, N.DATA_ENTRADA,
                CAST(SUM(((IIF(FN_PARAMETRO('SISTEMA.IGNORA_MARGEM_DEVOLUCAO') = '0',
                        (CAST(COALESCE(NE.VALOR_UNITARIO,0.0) AS DOUBLE PRECISION) + CAST(COALESCE(NE.MARGEM,0.0) AS DOUBLE PRECISION)),
                        CAST(COALESCE(NE.VALOR_UNITARIO,0.0) AS DOUBLE PRECISION))
                        )
                        * CAST(COALESCE(NE.QUANTIDADE,0.0) AS DOUBLE PRECISION)
                        - CAST(COALESCE(NE.VALOR_DESCONTO,0.0) AS DOUBLE PRECISION) 
                        - CAST(COALESCE(NE.MARGEM_DESCONTO,0.0) AS DOUBLE PRECISION)
                        + CAST(COALESCE(NE.VALOR_ACRESCIMO,0.0) AS DOUBLE PRECISION)
                        + CAST(COALESCE(NE.VALOR_IPI,0.0) AS DOUBLE PRECISION)
                        + CAST(COALESCE(NE.VALOR_PEDAGIO,0.0) AS DOUBLE PRECISION))
                        ) AS NUMERIC(18,2)) AS TOTAL_ITEM,
                        --SUM(NE.VALOR_TOTAL+NE.MARGEM-NE.MARGEM_DESCONTO+NE.VALOR_ACRESCIMO-NE.VALOR_DESCONTO+NE.VALOR_IPI+NE.VALOR_PEDAGIO) TOTAL_ITEM,
                        MAX((SELECT NT.VALOR_FRETE+NT.VALOR_ICMS_SUBST FROM TBNFE_TOTAIS NT WHERE N.CONTROLE = NT.NFE_CONTROLE)) TOTAL_OUTROS,
                        MAX((SELECT SUM(D.VALOR) FROM TBNF_DEVOLUCAO D WHERE D.ESTABELECIMENTO_ID = N.ESTABELECIMENTO_CODIGO AND D.NFE_ID = N.CONTROLE AND D.NFS_ID = 0 AND D.TIPO = 'R')) TOTAL_USADO
                    FROM TBNFE N, TBNFE_TOTAIS NT, TBNFE_ITEM NE, TBOPERACAO O
                WHERE N.CONTROLE         = :NFE_ID
                AND N.CONTROLE           = NE.NFE_CONTROLE
                AND N.CONTROLE           = NT.NFE_CONTROLE
                AND NE.OPERACAO_CODIGO   = O.CODIGO
                AND O.CONTROLE_DEVOLUCAO = '1'
                GROUP BY 1,2,3,4,5,6
            ) Z
        ) Z
        WHERE Z.SALDO > 0
      INTO :ESTABELECIMENTO_CODIGO, :SALDO
      DO
      BEGIN

          INSERT INTO TBNF_DEVOLUCAO (ESTABELECIMENTO_ID, NFE_ID, NFS_ID, PARCELAMENTO_ID, LANCAMENTO_ID, TIPO, VALOR)
             VALUES (:ESTABELECIMENTO_CODIGO, :NFE_ID, 0, 0, 0, 'R', :SALDO);

      END

END^

SET TERM ; ^