Erro de notas no LMC

>> Click aqui para Fazer o Download <<

Erro em notas do LMC.
Notas que não aparecem no relatório ou duplicam.






Abra o IBExpert
Conect o banco e abra o editor de procedures com CTRL+F12

Verifique se a opção "Use Current Connect" esta marcada.






São 4 SQLs para serem geradas, cole-as e execute (F9) uma por vez:

SQL 1: SC_130615_03_LMC_VENDAS.sql
SQL 2: SC_130618_01_LMC_GERA .sql
SQL 3: SC_130618_02_LMC_VENDAS_C.sql
SQL 4: SC_130621_01_LMC_COMPRAS_DTFUTURA.sql

SLQ 1:

 SET TERM ^ ;  
 ALTER procedure LMC_VENDAS (  
   EMPRESA integer,  
   DATA date,  
   COD_BOMBA integer)  
 returns (  
   EMP integer,  
   DTVENDA date,  
   CODIGO integer,  
   TANQUE integer,  
   BOMBA integer,  
   INICIO numeric(15,2),  
   FINAL numeric(15,2),  
   AFERICAO numeric(15,2),  
   QTDE numeric(15,2),  
   VLRVENDA numeric(15,2),  
   VLRVENDAACM numeric(15,2))  
 as  
 declare variable TIPOENCERRANTE char(1);  
 begin  
  select coalesce(cgencerrante, 'C') from a02acxg  
  where cgemp = :empresa and cgdata = :data  
  into :tipoencerrante;  
  if (:tipoencerrante = 'R') then  
   for select r.emp, r.dtvenda, r.codigo, r.tanque, r.bomba, r.inicio, r.final,  
         r.afericao, r.qtde, r.vlrvenda, r.vlrvendaacm  
     from lmc_vendas_r3(:empresa, :data, :cod_bomba) r  
   into :emp, :dtvenda, :codigo, :tanque, :bomba, :inicio, :final,  
      :afericao, :qtde, :vlrvenda, :vlrvendaacm  
   do begin  
    suspend;  
   end  
  else  
   for select r.emp, r.dtvenda, r.codigo, r.tanque, r.bomba, r.inicio, r.final,  
         r.afericao, r.qtde, r.vlrvenda, r.vlrvendaacm  
     from lmc_vendas_c(:empresa, :data, :cod_bomba) r  
   into :emp, :dtvenda, :codigo, :tanque, :bomba, :inicio, :final,  
      :afericao, :qtde, :vlrvenda, :vlrvendaacm  
   do begin  
    suspend;  
   end  
 end^  
 SET TERM ; ^  

SQL 2:

SET TERM ^ ;

ALTER procedure LMC_GERA (
    EMPRESA integer,
    DATA date,
    INATIVO char(1))
as
declare variable EMP integer;
declare variable COD integer;
declare variable TAQ integer;
declare variable VLR double precision;
declare variable COMPRA double precision;
declare variable VENDA double precision;
declare variable CTX integer;
begin
    inativo = Upper(:inativo) ;

    for select l.lmefemp, l.lmeftanque
        from a02almef l
        left join a02ataq t on (t.tqemp = l.lmefemp and t.tqnro = l.lmeftanque and l.lmefdata = :data)
        where l.lmefemp = :empresa
          and t.tqgnv = 'S'
    into :emp, :taq
    do begin
       delete from a02almef
       where lmefemp = :emp
         and lmeftanque = :taq
         and lmefdata = :data;
    end

    for select tqcodm
        from a02ataq t
        where tqemp = :empresa
          and tqgnv <> 'S'
          and tqcodm not in (select lmcod from a02almc e
                             where e.lmemp = t.tqemp
                               and e.lmdat = :data)
        group by tqcodm
    into :cod
    do begin
        insert into a02almc (lmemp, lmdat, lmcod)
        values (:empresa, :data, :cod);
    end

    for select tqnro, tqcodm
        from a02ataq t
        where tqemp = :empresa
          and tqgnv <> 'S'
          and tqnro not in (select lmeftanque from a02almef e
                            where e.lmefemp = t.tqemp
                              and e.lmefdata = :data)
    into :TAQ, :COD
    do begin
        select count(1)
        from a02abmtq x1
        where x1.btemp = :empresa
          and x1.bttanque = :taq
          and ((x1.btsit = 'A') or (:inativo = 'S'))
        into :ctx;

        if (:ctx = 0) then
        begin
          select sum(QTDE)
          from lmc_vendas(:empresa,  :data, null)
          where emp = :empresa
            and tanque = :taq
          into :VENDA;

          if (:VENDA IS NULL) then
            VENDA = 0;

          select sum(quantidade)
          from lmc_compras(:empresa, :data)
          where emp = :empresa
            and produto = :COD
            and tanque = :taq
          into :compra;

          if (:COMPRA IS NULL) then
            COMPRA = 0;

          if ((:VENDA + :COMPRA)> 0) then
            CTX = 1;
        end

        if (:ctx > 0) then
        begin
          insert into a02almef (lmefemp, lmefdata, lmefcodm, lmeftanque, lmefcompra, lmefvenda)
          values (:empresa, :data, :cod, :taq, :compra, :venda);
        end
    end

    for select x.lmeftanque, x.lmefcodm
        from a02almef x
        left join a02ataq t on (t.tqnro = x.lmeftanque and t.tqemp = x.lmefemp)
        where x.lmefemp = :empresa
          and x.lmefdata = :data
          and t.tqcodm <> x.lmefcodm
    into :taq, :cod
    do begin
       update a02almef set lmefcodm = :cod
       where lmefemp = :empresa
         and lmefdata = :data
         and lmeftanque = :taq;
    end

    for select y.lmefcodm, y.lmeftanque
        from a02almef y
        where y.lmefemp = :empresa
          and y.lmefdata = :data
    into :cod, :taq
    do begin
        compra = 0;
        select sum(quantidade)
        from lmc_compras(:empresa,  :data)
        where emp = :empresa
          and produto = :COD
          and tanque = :taq
        into :compra;

        if (:compra is null) then
          compra = 0;

        venda = 0;
        select sum(qtde)
        from lmc_vendas(:empresa,  :data, null)
        where emp = :empresa
          and codigo = :COD
          and tanque = :taq
        into :venda;

        if (:venda is null) then
            venda = 0;

        select first 1 x.lmefqtdf
        from a02almef x
        where x.lmefemp = :empresa
          and x.lmefcodm = :cod
          and x.lmeftanque = :taq
          and x.lmefdata between :data - 31 and :data -1
        order by x.lmefdata desc
        into :vlr;

        if (:vlr is null) then
          vlr = 0;

        update a02almef set
          lmefqtdi = :vlr,
          lmefcompra = :compra,
          lmefvenda = :venda
        where lmefemp = :empresa
          and lmefcodm = :cod
          and lmeftanque = :taq
          and lmefdata = :data;
    end
end^

SET TERM ; ^

  

SQL 3:

SET TERM ^ ;

ALTER procedure LMC_VENDAS_C (
    EMPRESA integer,
    DATA date,
    COD_BOMBA integer)
returns (
    EMP integer,
    DTVENDA date,
    CODIGO integer,
    TANQUE integer,
    BOMBA integer,
    INICIO numeric(15,2),
    FINAL numeric(15,2),
    AFERICAO numeric(15,2),
    QTDE numeric(15,2),
    VLRVENDA numeric(15,2),
    VLRVENDAACM numeric(15,2))
as
declare variable TAQ integer;
declare variable OLDTAQ integer;
declare variable OLDCOD integer;
declare variable COD integer;
declare variable INI numeric(15,2);
declare variable FIM numeric(15,2);
declare variable AFER numeric(15,2);
declare variable VEZ integer;
declare variable XDATA date;
declare variable XVLR numeric(15,2);
begin
  if (:cod_bomba is null) then
    cod_bomba = 0;
  emp = :empresa;
  dtvenda = :data;
  afericao = 0;
  final = 0;
  vlrvenda = 0;
  for select distinct bonro
      from a02abom b
      left join a02abmtq x on (x.btemp = b.boemp and x.btbomba = b.bonro)
      where b.boemp = :empresa
        and (coalesce(boativo, 'S') = 'S')
        and (b.bonro = :cod_bomba or :cod_bomba =0)
        and x.btsit = 'A'
      order by b.bonro
  into :bomba
  do begin
    oldtaq = 0;
    oldcod = 0;
    vez = 0;
    ini = null;
    for select m.mbtanque, m.mbcod, m.mbmaini, m.mbmafim,
               coalesce(m.mbafer,0),
              (m.mbtotal - m.mbdesc + m.mbacre)
        from a02acxg g
        join a02acxa c on (c.cxemp = g.cgemp and c.cxnrcx = g.cgnrcx)
        join a02acxmb m on (m.mbemp = c.cxemp and m.mbcaixa = c.cxcaixa)
        where g.cgemp = :empresa
          and g.cgdata = :data
          and m.mbbomba = :bomba
          and m.mbtanque is not null
        order by m.mbcod, m.mbtanque, c.cxordem, c.cxcaixa, m.mbmaini
    into :taq, :cod, :ini, :fim, :afer, :xvlr
    do begin
      if ((:taq <> :oldtaq) or (:cod <> :oldcod))  then
      begin
        if (:vez = 1) then
          suspend;
        oldtaq = :taq;
        oldcod = :cod;
        codigo = :COD;
        tanque = :taq;
        inicio = :ini;
        final  = :fim;
        qtde   = :fim - :ini;
        afericao = :afer;
        vlrvenda = :xvlr;
        vez = 1;
      end
      else
      begin
        final = :fim;
        qtde  = :qtde + (:fim - :ini);
        afericao = :afericao + :afer;
        vlrvenda = :vlrvenda + :XVLR;
      end
    end

    if (:ini is null) then
    begin
      select first 1 bttanque
      from a02abmtq
      where btemp = :empresa
        and btbomba = :bomba
        and btsit = 'A'
      into :tanque;

      select tqcodm
      from a02ataq
      where tqemp = :empresa
        and tqnro = :tanque
      into :codigo;

      for select first 60 m.mbmafim
          from a02acxa c
          join a02acxg g on (g.cgemp = c.cxemp and g.cgnrcx = c.cxnrcx)
          join a02acxmb m on (m.mbemp = c.cxemp and m.mbcaixa = c.cxcaixa)
          where c.cxemp = :empresa
            and g.cgdata between :data - 61 and :data -1
            and m.mbbomba = :bomba
            and m.mbtanque is not null
          order by c.cxcaixa desc, m.mbmaini desc
      into :ini do
      begin
        if (:ini is not null) then
          break;
        vez = :vez + 1;
      end

      if (:ini is null) then
      begin
        select boulreg
        from a02abom
        where boemp = :empresa
          and bonro = :bomba
        into :ini;
      end

      inicio = :ini;
      final  = :ini;
      afericao = 0;
      vlrvenda = 0;
      qtde     = 0;
    end

    qtde = :qtde - :afericao;
    xdata = :data - Extract(day from :data) + 1;

    select sum(m.mbtotal - m.mbdesc + m.mbacre)
    from a02acxa c
    join a02acxmb m on (m.mbemp = c.cxemp and m.mbcaixa = c.cxcaixa)
    where c.cxemp = :empresa
      and c.cxdata >= :xdata
      and c.cxdata < :data + 1
      and m.mbbomba = :bomba
      and m.mbtanque is not null
    into :vlrvendaacm;

    if (:vlrvendaacm is null) then
      vlrvendaacm = 0;
    suspend;
  end
end^

SET TERM ; ^

SQL 4:

CREATE OR ALTER PROCEDURE LMC_COMPRAS (
    empresa integer,
    data date)
returns (
    emp integer,
    dtentrada date,
    produto integer,
    nota integer,
    tanque integer,
    quantidade double precision,
    devolucao double precision)
as
declare variable data_ini date;
declare variable data_fim date;
begin
  data_ini = extract(day from :data) || '.'
          || extract(month from :data) || '.'
          || extract(year from :data) || ' 00:00:00';

  data_fim = extract(day from :data) || '.'
          || extract(month from :data) || '.'
          || extract(year from :data) || ' 23:59:59';

  for select n.ncemp,
             n.ncnro,
             m.medatamov,
             i.iccod,
             m.metanque,
             m.meqtd,
             coalesce(e.meqtd,0)
      from a02amove m
      join a02anfci i on (m.meemp = i.iccemp and m.menfe = i.icseq and m.meitnfe = i.icit)
      join a02anfc n  on (n.ncemp = i.iccemp and  n.ncseq = i.icseq)
      join a02ataq t  on (t.tqemp = m.meemp and t.tqnro = m.metanque)
      left join a02anfsv v on (v.nvemp = i.iccemp and v.nvnfseq = i.icnrdoc and v.nvitem = i.icitdoc)
      left join a02amove e on (e.meemp = v.nvemp and e.metipo = 'D'
                           and e.menfs = v.nvnfseq and e.meitnfs = v.nvitem
                           and e.metanque = t.tqnro)
      where n.ncemp = :empresa
        and m.medatamov between :data_ini and :data_fim
        and t.tqgnv <> 'S'
        and m.metanque is not null
        and (m.meqtd - coalesce(e.meqtd,0) > 0)
      order by n.ncemp, i.iccod, n.ncnro, m.metanque
  into :emp, :nota, :dtentrada, :produto, :tanque, :quantidade, :devolucao
  do begin
    suspend;
  end
end