p/A Farias - As store procedures nao funcionam porque ?

A Farias, gostaria de agradecer a sua grande cooperacao na ajuda
de muitas problemas no forum de muitos colegas programadores.


A Farias não sei o que está acontecendo fiz essas 3 SP e funcionava
no win98se ,ib 6.0 mas,ao migrar para firebird 1.5 que é otimo
rodando no conectiva 9 (linux) ,(migrei usando a sua dica de
backup no ib6 e restore no fb 1.5.) não funciona porque ?

OUTRA DUVIDA: COMO UTILIZAR CORRETO SUSPEND E EXIT

veja as sp:

ESSA SP E PARA BAIXAR OS PEDIDOS COMPRA AUTOMATICAMENTE
AO CONCLUIR A NF ENTRADA.


SET TERM ^ ;

ALTER PROCEDURE SP_BAIXA_PED (
M_CODFIL INTEGER,
M_CODFOR INTEGER,
M_NOTFIS CHAR(6))
AS
DECLARE VARIABLE M_ITENOT INTEGER;
DECLARE VARIABLE M_CODPRO VARCHAR(10);
DECLARE VARIABLE M_QTDMOV DOUBLE PRECISION;
DECLARE VARIABLE M_NP INTEGER;
DECLARE VARIABLE M_ITE INTEGER;
DECLARE VARIABLE M_CONTA INTEGER;
DECLARE VARIABLE M_SALDO INTEGER;
DECLARE VARIABLE M_QTDPED DOUBLE PRECISION;
DECLARE VARIABLE M_ITEMX INTEGER;
DECLARE VARIABLE M_QTDDES DOUBLE PRECISION;
begin
FOR SELECT itenot,codpro,qtdmov FROM itnfent
WHERE (codfil=:M_CODFIL) AND (notfis=:M_NOTFIS) AND (codfor=:M_CODFOR)
INTO :M_ITENOT,:M_CODPRO, :M_QTDMOV
DO
BEGIN
m_itemx = 0;

for select iteped,qtdped from vw_pedcom
where (codfil=:m_codfil) and (codfor=:m_codfor) and (situac=´A´)
and (codpro=:m_codpro) into :m_ite,:m_qtdped
do
begin
if (:m_qtdmov = :m_qtdped) then
begin
m_itemx = :m_ite;
end
suspend;
end
m_qtddes = 0;

for select numped,iteped,qtdped from vw_pedcom
where (codfil=:m_codfil) and (codfor=:m_codfor) and (situac=´A´)
and (codpro=:m_codpro) into :m_np,:m_ite,:m_qtdped
do
begin
if ((:m_itemx > 0) and (:m_itemx = :m_ite)) then
begin
UPDATE itpedcom SET qtdrec = (qtdrec + :M_QTDped)
WHERE (codfil=:M_CODFIL) AND (numped=:M_np) and (iteped=:m_ite);
end

if ((:m_itemx = 0) and ((:m_qtddes + :m_qtdped) <= :m_qtdmov)) then
begin
UPDATE itpedcom SET qtdrec = (qtdrec + :M_QTDped)
WHERE (codfil=:M_CODFIL) AND (numped=:M_np) and (iteped=:m_ite);
m_qtddes = m_qtddes + :m_qtdped;
end

suspend;
end

UPDATE itnfent SET situac = ´D´
WHERE (codfil=:M_CODFIL) AND (notfis=:M_notfis) and (codfor=:m_codfor)
and (itenot=:m_itenot);

SUSPEND;
END

UPDATE nfent SET situac = ´D´
WHERE (codfil=:M_CODFIL) AND (notfis=:M_notfis) and (codfor=:m_codfor);

m_conta = 0;

FOR SELECT numped,iteped,(qtdped - qtdrec) FROM itpedcom
WHERE (codfil=:M_CODFIL) AND (situac=´A´) AND (numped=:M_np)
INTO :m_np,:M_ITE,:m_saldo
DO
BEGIN
UPDATE itpedcom SET situac = ´D´
WHERE (codfil=:M_CODFIL) AND (numped=:M_np) and (iteped=:m_ite)
and (qtdped - qtdrec) = 0;

if (:m_saldo > 0) then
m_conta = m_conta + 1;

suspend;
END
UPDATE pedcom SET situac = ´D´
WHERE (codfil=:M_CODFIL) AND (numped=:M_np) and (codfor=:m_codfor)
and (:m_conta = 0);

EXIT;
end

^

SET TERM ; ^

*****************************************************

ESSA SP GRAVA EM UM ARQUIVO AS ETIQUETAS DE PRODUTOS
PARA EMISSAO

SET TERM ^ ;

ALTER PROCEDURE SP_GERA_ETIQ (
M_NOTFIS CHAR(6),
M_CODFOR INTEGER,
M_CODFIL INTEGER)
AS
DECLARE VARIABLE M_CHAVE CHAR(10);
DECLARE VARIABLE M_VALIDA DATE;
DECLARE VARIABLE M_LOTE CHAR(8);
DECLARE VARIABLE M_CODPRO CHAR(7);
DECLARE VARIABLE M_QTDMOV INTEGER;
DECLARE VARIABLE I INTEGER;
begin
delete from etiqpro;
FOR SELECT qtdmov,codpro,lote,valida,chave FROM vw_nfent
where codfil=:m_codfil and notfis=:m_notfis and codfor=:m_codfor
INTO :m_qtdmov, :m_codpro,:m_lote,:m_valida,:m_chave
DO
BEGIN
if (:m_chave is not NULL) then
begin
i = 1;
WHILE (i <= :m_qtdmov) DO
BEGIN
INSERT INTO etiqpro (codpro,codlot,valida,lote,codfor,codfil) VALUES (:m_codpro, :m_chave,:m_valida,:m_lote,:m_codfor,:m_codfil);
i = i + 1;
END

end
suspend;
end
exit;
end

^

SET TERM ; ^

*******************************************
ESSA SP CALCULA O CONSUMO MEDIO DOS PRODUTOS

SET TERM ^ ;

ALTER PROCEDURE SP_CONSMED (
M_CODFIL INTEGER,
M_ANOE SMALLINT,
M_MESE SMALLINT)
AS
DECLARE VARIABLE M_CODPRO VARCHAR(10);
DECLARE VARIABLE M_ANO SMALLINT;
DECLARE VARIABLE M_MES SMALLINT;
DECLARE VARIABLE M_QTD INTEGER;
DECLARE VARIABLE M_COD VARCHAR(10);
DECLARE VARIABLE M_CONMED INTEGER;
DECLARE VARIABLE M_IR DOUBLE PRECISION;
DECLARE VARIABLE M_CM DOUBLE PRECISION;
DECLARE VARIABLE M_EM DOUBLE PRECISION;
DECLARE VARIABLE M_TR DOUBLE PRECISION;
DECLARE VARIABLE M_SM DOUBLE PRECISION;
DECLARE VARIABLE M_PR DOUBLE PRECISION;
begin
m_cod = ´*´;
FOR SELECT codpro,ano,mes,qtd FROM vw_consmed
WHERE (codfil=:M_CODFIL) and (ano >= :m_anoe) and (qtd > 0)
INTO :M_codpro,:M_ano,:m_mes,:M_QTD
DO
BEGIN
if (:m_cod <> :m_codpro) then
begin
if (:m_conmed > 0) then
begin
m_cm = (:m_conmed / :m_ir);
select tr from produto
where (codfil=:m_codfil) and (codpro=:m_cod) into :m_tr;

m_em = (:m_cm * :m_tr) / 2;
m_sm = (:m_cm * :m_ir) + :m_em;
m_pr = (:m_cm * :m_tr) + :m_em;
update produto set conmed=:m_cm,
qtdmin=:m_em,
qtdmax=:m_sm,
ponres=:m_pr,
ir=:m_ir
where (codfil=:m_codfil) and (codpro=:m_cod);
end
m_conmed = 0;
m_ir = 0;
m_cod = :m_codpro;
end
if (((:m_mes >= :m_mese) and (:m_ano=:m_anoe)) or ((:m_mes <= :m_mese) and (:m_ano <>:m_anoe))) then
begin
m_conmed = m_conmed + :m_qtd;
m_ir = m_ir + 1;
end
SUSPEND;
END
if (:m_conmed > 0) then
begin
m_cm = (:m_conmed / :m_ir);
select tr from produto where (codfil=:m_codfil) and (codpro=:m_cod)
into :m_tr;
m_em = (:m_cm * :m_tr) / 2;
m_sm = (:m_cm * :m_ir) + :m_em;
m_pr = (:m_cm * :m_tr) + :m_em;
update produto set conmed=:m_cm,qtdmin=:m_em,qtdmax=:m_sm,ponres=:m_pr,ir=:m_ir
where codfil=:m_codfil and codpro=:m_cod;
end

suspend;
end

^

SET TERM ; ^