PER Full Person Details

select hrl.meaning || decode(hrl.meaning,null,null,' ') || peo.first_name || 
       decode(peo.first_name,null,null,' ') || peo.last_name name,
       peo.comment_id comment_id1,
      --&c_emp_df_details conc_emp_df,
	  &c_emp_df_details conc_emp_df1,
      &c_emp_df_label conc_emp_label_df,
       peo.middle_names middle_names,
       peo.known_as known_as,
       hrl2.meaning sex,
       peo.previous_last_name previous_surname,
       hrl3.meaning registered_disabled,
       peo.date_of_birth date_of_birth,
       hrl4.meaning marital_status,
       hrl5.meaning nationality,
       peo.national_identifier national_identifier,
       hr_person_type_usage_info.get_user_person_type(peo.effective_end_date,peo.person_id) person_type,
       peo.employee_number employee_number,
       peo.applicant_number applicant_number,
       pp.phone_number work_tel_no,
       peo.email_address email_address,
       hrl6.meaning expense_cheque,
       peo.effective_start_date effective_start_date_1,
       peo.effective_end_date effective_end_date_1,
       peo.person_id person_id_1,
       peo.PER_INFORMATION_CATEGORY,
       decode(:p_town_of_birth,null,peo.town_of_birth,hr_general.decode_lookup(:p_town_of_birth, peo.town_of_birth)) town_of_birth,
       decode(:p_region_of_birth,null,peo.region_of_birth,hr_general.decode_lookup(:p_region_of_birth,peo.region_of_birth)) region_of_birth,
       ftl.territory_short_name country_of_birth, 
	PER_PERRPFP1_XMLP_PKG.c_get_emp_leg_dfformula(peo.PER_INFORMATION_CATEGORY) C_get_emp_leg_df
from   per_all_people_f peo,
       per_phones pp,
       hr_lookups hrl,
       hr_lookups hrl2,
       hr_lookups hrl3,
       hr_lookups hrl4,
       hr_lookups hrl5,
       hr_lookups hrl6,
       fnd_territories_vl ftl
where  peo.person_id = :p_person_id
  and  pp.parent_table (+) = 'PER_ALL_PEOPLE_F'
  and  pp.phone_type  (+) = 'W1'
  and  pp.parent_id (+) = peo.person_id
  and  :p_session_date between nvl(pp.date_from,:p_session_date)
  and  nvl(pp.date_to,:p_session_date)
  and  hrl.lookup_type  (+) = 'TITLE'
  and  hrl.lookup_code  (+) = peo.title
  and  hrl2.lookup_type (+) = 'SEX'
  and  hrl2.lookup_code (+) = peo.sex
  and  hrl3.lookup_type (+) = 'YES_NO'
  and  hrl3.lookup_code (+) = peo.registered_disabled_flag
  and  hrl4.lookup_type (+) = 'MAR_STATUS'
  and  hrl4.lookup_code (+) = peo.marital_status
  and  hrl5.lookup_type (+) = 'NATIONALITY'
  and  hrl5.lookup_code (+) = peo.nationality
  and  hrl6.lookup_type (+) = 'HOME_OFFICE'
  and  hrl6.lookup_code (+) = peo.expense_check_send_to_address
  and  peo.business_group_id = :P_BUSINESS_GROUP_ID
  and  peo.country_of_birth = ftl.territory_code (+)
order by peo.effective_start_date