Oracle Receivable Contacts Query

SELECT DISTINCT

  (rc.first_name||' '||rc.last_name) name,

  rc.title            ,

  DECODE(HCP.PHONE_LINE_TYPE,'GEN',HCP.PHONE_AREA_CODE ||'-' ||HCP.PHONE_NUMBER,NULL) "tel_number",

  hcp.phone_extension "Extension"      ,

  DECODE(HCP.PHONE_LINE_TYPE,'FAX',HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER,NULL) FAX_NUMBER ,

  rc.email_address                    ,

  hca.account_number "Legacy customer",

FROM hz_parties hp          ,

  ra_contacts rc               ,

  hz_cust_site_uses_all hcsu   ,

  hz_cust_acct_sites_all hcas  ,

  hz_party_sites hps           ,

  HZ_CUST_ACCOUNTS hca         ,

  hz_party_relationships hprel ,

  HZ_CONTACT_POINTS HCP

WHERE 1 = 1

   AND hcsu.cust_acct_site_id        =hcas.cust_Acct_Site_id

   AND hcas.party_site_id            =hps.party_site_id

   AND rc.contact_id                 =hcSU.CONTACT_ID

   AND rc.party_relationship_id      = hprel.party_relationship_id

   AND hprel.party_id                = hcp.owner_table_id

   AND hprel.party_relationship_type = 'CONTACT_OF'

   AND rc.status                     = 'A'

   AND hcp.status                    = 'A'

   AND hcp.contact_point_type        ='PHONE'

   AND hp.party_id                   =hps.party_id

   AND HCA.CUST_ACCOUNT_ID           =HCAS.CUST_ACCOUNT_ID

   AND hcsu.site_use_code            ='SHIP_TO'

   AND HCSU.STATUS                   = 'A'

   AND hcas.org_id                   =736;

   --AND hcas.org_id =457;