CREATE OR REPLACE PROCEDURE FindClient (i_msisdn IN VARCHAR2,
                                        o_activestop OUT VARCHAR2,
										o_balance OUT NUMBER,
										o_subcosid OUT NUMBER,
										o_state OUT VARCHAR2,
										o_result OUT NUMBER )IS

p_suspendstop VARCHAR2( 20 );
p_disablestop VARCHAR2( 20 );
p_fraudlock NUMBER;
p_accountstate NUMBER;
BEGIN
o_result:= 0 ;
SELECT "accountstate", "balance", "fraudlock", "activestop","suspendstop","disablestop","subcosid" 
INTO p_accountstate, o_balance, p_fraudlock, o_activestop,p_suspendstop,p_disablestop,o_subcosid
FROM basetab_pps@informix WHERE "msisdn"=i_msisdn;
IF p_fraudlock<> 0  THEN o_result:=- 1 ;  
END IF;
IF o_result= 0  THEN 
IF SYSDATE>TO_DATE(p_disablestop,'YYYY-MM-DD') THEN o_result:='-2';   
ELSIF  SYSDATE>TO_DATE(p_suspendstop,'YYYY-MM-DD') THEN o_state:='04';
ELSIF  SYSDATE>TO_DATE(o_activestop,'YYYY-MM-DD') THEN o_state:='03';
ELSE o_state :='02';
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN 
o_result:=- 3 ; 
END;