i have a function: - CREATE OR REPLACE FUNCTION fn_confirm_sms("varchar", "varchar", "varchar", "varchar", "varchar")
-
RETURNS "varchar" AS
-
$BODY$
-
declare
-
v_user_mobile_no varchar(30);
-
v_return varchar;
-
v_paying_agent_cd varchar(30);
-
v_user_agent_cd varchar(30);
-
v_amt numeric(12,2);
-
v_amount numeric(12,2);
-
v_stage varchar(1);
-
v_benef varchar(50);
-
v_remit_by varchar(50);
-
-
-
begin
-
-
select user_mobile_no,paying_agent_cd
-
into v_user_mobile_no,v_user_agent_cd
-
from users
-
where name = $1;
-
-
if v_user_mobile_no = $4 then
-
-
-
-
select paying_agent_code,to_number(amount,999999999.99),stage,benef_name,remit_by
-
into v_paying_agent_cd,v_amt,v_stage,v_benef ,v_remit_by
-
from remittance_info
-
where rad_no = $2;
-
-
if v_stage = '1' then
-
v_amount = v_amt-500;
-
-
if v_paying_agent_cd = v_user_agent_cd then
-
if v_amt = to_number($3,999999999.99) then
-
update remittance_info set stage=3,remittance_confirm_dt=now(),info_id=$5 where rad_no=$2;
-
-
-
if v_remit_by = 'web' then
-
insert into diposit (paying_agent_code,amount,diposit_entry_date,diposit_by,diposit_type,
-
remarks,rad_no,benef_name,comm,media) values(v_paying_agent_cd,v_amt,now(),$1,'dr','',$2,v_benef,'100.00',$4);
-
else
-
insert into diposit (paying_agent_code,amount,diposit_entry_date,diposit_by,diposit_type,
-
remarks,rad_no,benef_name,comm,media) values(v_paying_agent_cd,v_amount,now(),$1,'dr','',$2,v_benef,'0.00',$4);
-
end if;
-
-
return 'Confirmed Successfully to'||' '||v_benef;
-
-
else
-
return 'Invalid Amount';
-
end if;
-
-
else
-
return 'Paying Agent Not Found';
-
end if;
-
-
elsif v_stage='3' then
-
return 'Already Confirmed';
-
else
-
return 'Not Authorized';
-
end if;
-
else
-
return 'N/A';
-
end if;
-
-
end$BODY$
-
LANGUAGE 'plpgsql' VOLATILE;
i want to return two values after confirmed successfully like
return 'Confirmed Successfully to'||' '||v_benef ,$5;
but i don't have the idea
please help me
thanks in advance
|
