// ***************************************************************************
// ***************************************************************************
public ResultSet getPERSON (int person_id){

ResultSet rs=null;
 

	try {
                CallableStatement call = con.prepareCall ("{ ? = call pk_PERSON.queryPERSON(?) }");
                call.registerOutParameter (1, OracleTypes.CURSOR);
		call.setInt(2,person_id);

                call.execute ();
                rs = (ResultSet)call.getObject (1);


        }
        catch ( SQLException sqlexecp) {
                sqlexecp.printStackTrace();
        }

        return (rs) ;
}
// ***************************************************************************
// ***************************************************************************
 public void getDataFromDatabase( ResultSet rs ) {
try {
 
	Vector plist = new Vector();

        while ( rs.next() ){
                Person aPerson = new Person();

                aPerson.personid= rs.getInt("PERSON_ID");
                aPerson.personlogin= rs.getString("PERSON_LOGIN");
                aPerson.personpassword= rs.getString("PERSON_PASSWORD");
                aPerson.personfname= rs.getString("PERSON_FNAME");
                aPerson.personlname= rs.getString("PERSON_LNAME");
                aPerson.personssnum= rs.getString("PERSON_SS_NUM");
                aPerson.personbdate= rs.getString("PERSON_BDATE");
                aPerson.personemail= rs.getString("PERSON_EMAIL");
                aPerson.personworkphone= rs.getString("PERSON_WORK_PHONE");
                aPerson.personbirthfatherpersonid= rs.getInt("PERSON_BIRTH_FATHER_PERSON_ID");
                aPerson.personbirthmotherpersonid= rs.getInt("PERSON_BIRTH_MOTHER_PERSON_ID");
 
                plist.addElement(aPerson);
        }
 }
 catch( Exception ex ) {
        ex.printStackTrace();
 }
 
} 

// ***************************************************************************
// ***************************************************************************

  create or replace package pk_PERSON IS
	TYPE PERSONRecord IS RECORD
	( 
 			 personid PERSON.PERSON_ID%type,
 			 personlogin PERSON.PERSON_LOGIN%type,
 			 personpassword PERSON.PERSON_PASSWORD%type,
 			 personfname PERSON.PERSON_FNAME%type,
 			 personlname PERSON.PERSON_LNAME%type,
 			 personssnum PERSON.PERSON_SS_NUM%type,
 			 personbdate varchar2(80),
 			 personemail PERSON.PERSON_EMAIL%type,
 			 personworkphone PERSON.PERSON_WORK_PHONE%type,
 			 personbirthfatherpersonid PERSON.PERSON_BIRTH_FATHER_PERSON_ID%type,
 			 personbirthmotherpersonid PERSON.PERSON_BIRTH_MOTHER_PERSON_ID%type
  
 );


type byPERSON is ref cursor return PERSONRecord;

	

function queryPERSON(Id number)  return byPERSON;
END pk_PERSON;

// ***************************************************************************
// ***************************************************************************
create or replace package body pk_PERSON IS
	function queryPERSON (ID number)return byPERSON is rc byPERSON;
	BEGIN
       		open rc for
                	SELECT 
 			t1.PERSON_ID,
  			PERSON_LOGIN,
  			PERSON_PASSWORD,
  			PERSON_FNAME,
  			PERSON_LNAME,
  			PERSON_SS_NUM,
  			to_char(PERSON_BDATE,'MM-DD-YYYY') PERSON_BDATE,
  			PERSON_EMAIL,
  			PERSON_WORK_PHONE,
  			PERSON_BIRTH_FATHER_PERSON_ID,
  			PERSON_BIRTH_MOTHER_PERSON_ID
	FROM 
		PERSON t1, FAMILY_PERSON t2
	WHERE 
		t1.person_id != ID and
   		t1.person_id = t2.person_id and
		t2.STATUS > 0 and
		t2.FAMILY_ID in ( select FAMILY_ID from FAMILY_PERSON where
		PERSON_ID = ID and STATUS > 0);

	return rc;
        END;

END pk_PERSON;