與ACE*COMM後付費後接口
create or replace procedure get_jbill_info(strBuffer in varchar,strReturn out varchar)
is
strNo varchar(10);–trade Number
strMobile varchar(20);–Mobile Number
strPassword varchar(20);–Mobile Pin
flag boolean;–trade state
otherstr varchar(100);
strBalance varchar(30);
begin
flag:=false;
–select substr(strBuffer,0,indexOf(strBuffer,’,')-1),substr(strBuffer,indexof(strBuffer,’,')+1,length(strBuffer)) into strNo,otherStr from dual;
strNo:=substr(strBuffer,0,indexOf(strBuffer,’,')-1);
otherstr:=substr(strBuffer,indexof(strBuffer,’,')+1,length(strBuffer));
if strNo=’9810′ then –login
begin
–select substr(otherStr,0,indexOf(otherStr,’,')-1),substr(otherStr,indexof(otherStr,’,')+1,length(otherStr)) into strMobile,otherStr from dual;
–select otherStr into strPassword from dual;
strmobile:=’0′||substr(otherStr,0,indexOf(otherStr,’,')-1);
otherstr:=substr(otherStr,indexof(otherStr,’,')+1,length(otherStr));
strpassword:=substr(otherstr,0,indexof(otherstr,’,')-1);
callcentre.authenticate_service_password(strMobile,strPassword);
flag:=true;
end;
elsif strNo=’9811′ then–change password
begin
–select substr(otherStr,0,indexOf(otherStr,’,')-1),substr(otherStr,indexof(otherStr,’,')+1,length(otherStr)) into strMobile,otherStr from dual;
–select otherStr into strPassword from dual;
strmobile:=’0′||substr(otherStr,0,indexOf(otherStr,’,')-1);
otherstr:=substr(otherStr,indexof(otherStr,’,')+1,length(otherStr));
strpassword:=substr(otherstr,0,indexof(otherstr,’,')-1);
callcentre.change_service_password(strMobile,strPassword);
flag:=true;
end;
elsif strNo=’9812′ then –balance
begin
–select substr(otherStr,0,indexOf(otherStr,’,')-1),substr(otherStr,indexof(otherStr,’,')+1,length(otherStr)) into strMobile,otherStr from dual;
strmobile:=’0′||substr(otherStr,0,indexOf(otherStr,’,')-1);
strBalance:=callcentre.get_customer_balance(strMobile);
flag:=true;
end;
end if;
–select substr(otherStr,0,indexOf(otherStr,’,')-1),substr(otherStr,indexof(otherStr,’,')+1,length(otherStr)) into strMobile,otherStr from dual;
–select otherStr into strPassword from dual;
–strReturn:=strNo||’ ‘||strMobile||’ ‘||strPassword;
if flag=true then
strReturn:=’00000,0,trade success,’||replace(strBalance,’.',”)||’00,’;–trade success
end if;
exception
when others then
strReturn:=’11111,0,trade failure,’;–trade failure
end;
drop database link JBILL_CALLCENTRE.BTTB;
– Create database link
create database link JBILL_CALLCENTRE.BTTB
connect to JBILL_IVR identified by T5H9I7
using ‘prod’;
- Create the synonym
create or replace synonym CALLCENTRE
for JAN.IVR@JBILL_CALLCENTRE.BTTB;