|
Hola:
La funcion de Postgres:
CREATE OR REPLACE FUNCTION "public"."card_for_emission" (pbin varchar, ptype integer, puser_id varchar, out result_table "pg_catalog"."refcursor") RETURNS "pg_catalog"."refcursor" AS
$body$
declare
bankpid integer;
seq_pid integer;
begin
select bank_pid into bankpid
from bin_def
where bin_def.bin_id = pbin;
if bankpid is null then
raise exception 'Invalid Bin for get card to emission';
end if;
if ptype = 1 then
-- People Card TABLE
open result_table for
select
distinct c.cardnumber, p.name, p.last_name, p.last_name2,
c.exp_date, c.card_type
from card c
inner join cardholder ch on
c.cardholder_pid = ch.cardholder_pid
inner join client cl on
cl.client_pid = ch.client_pid
inner join bank b on
b.bank_pid = cl.bank_pid
inner join people p on
p.people_pid = cl.people_pid
inner join bin_def bd on
bd.bank_pid = b.bank_pid
where
c.status = 1 and b.bank_pid = bankpid and c.printlot = 0;
insert into record_log (log_timestamp, log_table, log_table_pid,
log_user, log_action, details, log_errorcode)
values(CURRENT_TIMESTAMP(2), 'card', NULL, puser_id, 4 ,
'select card for PRINT FRONT: status=1' ||
' bank_pid='|| bankpid ||
' printlot= 0', null);
end if;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
En Delphi tengo el siguiente codigo en el Create del formulario principal:
with DM do
begin
dsPrintFront.Dataset := spPrintFront;
MEmisor_Connection.Open;
spPrintFront.Parameters.ParamByName('pbin').Value := '977000';
spPrintFront.Parameters.ParamByName('ptype').Value := 1;
spPrintFront.Parameters.ParamByName('puser_id').Value := 'reina';
spPrintFront.Prepared := True;
spPrintFront.Open;
end;
En dicho formulario tengo un DBGrid y en tiempo de diseño puse DataSource = DM.dsPrintFront
Saludos,
Reina
|