SELECT party.party_name Borrower_Name
,org.name operating_unit
,loan.loan_number
,contact_person.party_name contact_person_name
,con_phone.raw_phone_number contact_phone_number
FROM hz_parties party
,lns_loan_headers_all loan
,hr_all_organization_units_tl org
,hz_parties contact_person
,hz_contact_points con_phone
WHERE party.party_id = loan.primary_borrower_id
AND org.organization_id = loan.org_id
AND loan.contact_pers_party_id = contact_person.party_id(+)
AND con_phone.owner_table_name(+) = 'HZ_PARTIES'
AND con_phone.owner_table_id(+) = loan.contact_rel_party_id
AND con_phone.primary_flag(+) = 'Y'
AND con_phone.status(+) = 'A'
AND con_phone.contact_point_type(+) = 'PHONE'
AND loan.loan_id = :p_loan_id
