--SQL START--
SET TERM ^ ;

CREATE OR ALTER PROCEDURE VENDAS_ESPHERA (
    FILIAL INTEGER,
    DATA_INICIAL TIMESTAMP,
    DATA_FINAL TIMESTAMP)
RETURNS (
    ID_VENDA VARCHAR(20),
    CHAVE_NFE varchar(40),
    TOTAL NUMERIC(15,2), 
    DATA TIMESTAMP,
    CANCELADA CHAR(1),
    OPERACAO CHAR(1))
AS
begin
for select
  ne.nota * 100 + ne.filial id_venda,
  ne.chave_nfe,
  ne.total,
  ne.data,
  ne.cancelada,
  ne.opfinanceiro operacao
from
  notas_emitidas ne
where
  ne.filial = :Filial
  and ne.opfinanceiro in ('+', '-')
  and ne.data > :Data_Inicial
  and ne.data <= :Data_Final
into
  :id_venda, chave_nfe, :total, :data, :cancelada, :operacao do
suspend;

for select
  nr.nota * 100 + nr.filial id_venda,
  nr.chave_edoc,
  nr.total,
  nr.data,
  'N' cancelada,
  '-' operacao
from
  notas_recebidas nr
where
  nr.filial = :Filial
  and nr.opfinanceiro = '-'
  and nr.tipo = 'F'
  and nr.data >= :Data_Inicial
  and nr.data < :Data_Final + 1
into :id_venda, :chave_nfe, :total, :data, :cancelada, :operacao do
suspend;

end
^

SET TERM ; ^

GRANT SELECT ON NOTAS_EMITIDAS TO PROCEDURE VENDAS_ESPHERA;
GRANT SELECT ON NOTAS_RECEBIDAS TO PROCEDURE VENDAS_ESPHERA;

GRANT EXECUTE ON PROCEDURE VENDAS_ESPHERA TO ACSOR;

--SQL END--
