OKL Notify Repossession

               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_ACCOUNT_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