Thread: Function doesn't work properly
Hi all.
I wrote a function and it does not work properly. What am I doing wrong?
*- Here is the script .
---
CREATE OR REPLACE FUNCTION "public"."cxp_balfactura" (x_codcia char, x_suplidor char, x_factura char, x_monto numeric, x_moneda integer) RETURNS numeric AS
$body$
DECLARE
cursor1 record;
debitos numeric(14,2);
creditos numeric(14,2);
balance numeric (14,2);
BEGIN
balance := x_monto;
$body$
DECLARE
cursor1 record;
debitos numeric(14,2);
creditos numeric(14,2);
balance numeric (14,2);
BEGIN
balance := x_monto;
--It doesn't execute this lines, why?
for cursor1 in select cxp_mov_item.tipo_transaccion,sum(item_monto+item_descuento) AS monto
from cxp_mov_item,cxp_mov_head
where cxp_mov_item.cia_codigo = cxp_mov_head.cia_codigo
and cxp_mov_item.mov_codigo = cxp_mov_head.mov_codigo
and cxp_mov_item.fact_codigo = x_factura
and cxp_mov_head.cia_codigo = x_codcia
and cxp_mov_head.supl_codigo = x_suplidor
and cxp_mov_head.id_moneda = x_moneda
and cxp_mov_head.mov_estado <> '*'
GROUP by cxp_mov_item.tipo_transaccion
loop
if cursor1.tipo_transaccion = 'ND' then
debitos := debitos + cursor1.monto;
end if;
from cxp_mov_item,cxp_mov_head
where cxp_mov_item.cia_codigo = cxp_mov_head.cia_codigo
and cxp_mov_item.mov_codigo = cxp_mov_head.mov_codigo
and cxp_mov_item.fact_codigo = x_factura
and cxp_mov_head.cia_codigo = x_codcia
and cxp_mov_head.supl_codigo = x_suplidor
and cxp_mov_head.id_moneda = x_moneda
and cxp_mov_head.mov_estado <> '*'
GROUP by cxp_mov_item.tipo_transaccion
loop
if cursor1.tipo_transaccion = 'ND' then
debitos := debitos + cursor1.monto;
end if;
if cursor1.tipo_transaccion = 'CK' then
creditos := creditos + cursor1.monto;
end if;
creditos := creditos + cursor1.monto;
end if;
if cursor1.tipo_transaccion = 'NC' then
creditos := creditos + cursor1.monto;
end if;
end loop;
creditos := creditos + cursor1.monto;
end if;
end loop;
if balance is null then
balance:=0;
end if;
balance:=0;
end if;
if creditos is null then
creditos := 0;
end if;
creditos := 0;
end if;
if debitos is null then
debitos := 0;
end if;
debitos := 0;
end if;
balance := balance + creditos - debitos;
update cxp_facturas set fact_balance = balance
where cia_codigo = x_codcia
and fact_tipo = substr(x_factura,1,2)
and fact_codigo = substr(x_factura,3)
and supl_codigo = x_suplidor;
where cia_codigo = x_codcia
and fact_tipo = substr(x_factura,1,2)
and fact_codigo = substr(x_factura,3)
and supl_codigo = x_suplidor;
return balance;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
--
Thanks in advance.
On 14 Aug 2009, at 18:42, Ing. Eris J. Gómez wrote: > Hi all. > > I wrote a function and it does not work properly. What am I doing > wrong? > *- Here is the script .. > > --- > CREATE OR REPLACE FUNCTION "public"."cxp_balfactura" (x_codcia char, > x_suplidor char, x_factura char, x_monto numeric, x_moneda integer) > RETURNS numeric AS > $body$ > DECLARE > cursor1 record; > debitos numeric(14,2); > creditos numeric(14,2); > balance numeric (14,2); > BEGIN > balance := x_monto; > > --It doesn't execute this lines, why? What makes you think that? > > for cursor1 in select > cxp_mov_item.tipo_transaccion,sum(item_monto+item_descuento) AS monto > from cxp_mov_item,cxp_mov_head > where cxp_mov_item.cia_codigo = cxp_mov_head.cia_codigo > and cxp_mov_item.mov_codigo = cxp_mov_head.mov_codigo > and cxp_mov_item.fact_codigo = x_factura > and cxp_mov_head.cia_codigo = x_codcia > and cxp_mov_head.supl_codigo = x_suplidor > and cxp_mov_head.id_moneda = x_moneda > and cxp_mov_head.mov_estado <> '*' > GROUP by cxp_mov_item.tipo_transaccion Does this query return any rows? > loop You can raise a few notices in the loop body to see what's going on. The value of tipo_transaccion is probably interesting. > if cursor1.tipo_transaccion = 'ND' then > debitos := debitos + cursor1.monto; > end if; > > if cursor1.tipo_transaccion = 'CK' then > creditos := creditos + cursor1.monto; > end if; > > if cursor1.tipo_transaccion = 'NC' then > creditos := creditos + cursor1.monto; > end if; > end loop; Alban Hertroys -- Screwing up is the correct approach to attaching something to the ceiling. !DSPAM:737,4a859c8010135542721450!