OKL Transfer and Assumption

 SELECT CUST.PARTY_NAME,
        CUST.ADDRESS1 PARTY_ADDRESS1,
        CUST.ADDRESS2 PARTY_ADDRESS2,
        CUST.ADDRESS3 PARTY_ADDRESS3,
        CUST.ADDRESS4 PARTY_ADDRESS4,
        CUST.CITY PARTY_CITY,
        CUST.POSTAL_CODE PARTY_POSTAL_CODE,
        CUST.STATE PARTY_STATE,
        CUST.PROVINCE PARTY_PROVINCE,
        CUST.COUNTY PARTY_COUNTY,
        CUST.EMAIL_ADDRESS PARTY_EMAIL_ADDRESS,
        CNT.PARTY_NAME CONTACT_NAME,
        CNT.PERSON_ACADEMIC_TITLE CONTACT_TITLE,
        CNT.PRIMARY_PHONE_COUNTRY_CODE||' '||CNT.PRIMARY_PHONE_AREA_CODE||' '||
        CNT.PRIMARY_PHONE_NUMBER||' '||CNT.PRIMARY_PHONE_EXTENSION CONTACT_PRIMARY_PHONE
 FROM HZ_PARTIES CUST,
      HZ_CUST_ACCOUNTS CSTACC,
      HZ_PARTY_RELATIONSHIP_V REL,
      HZ_PARTIES CNT
WHERE CSTACC.CUST_ACCOUNT_ID = :CUST_ACCT_ID
AND CSTACC.PARTY_ID = CUST.PARTY_ID
AND CUST.PARTY_ID = REL.OBJECT_ID(+)
AND REL.RELATIONSHIP_TYPE_CODE = 'CONTACT_OF'
AND CNT.PARTY_ID(+) = REL.SUBJECT_ID
AND ROWNUM = 1