AR Customer Query

SELECT DISTINCT hcasa.org_id "Operating Unit Name" ,
  hp.party_name "Customer Name"                    ,
  hcas.account_number "Customer Number"            ,
  hl.address1 "Address 1"                          ,
  hl.address2 "Address 2"                          ,
  hl.city "City"                                   ,
  hl.state "State"                                 ,
  hl.postal_code "Postal Code"                     ,
  hl.country "Country"                             ,
  hps.party_site_number "Site Number"              ,
  hcsua.STATUS "Active Flag"                       ,
  hcsua.site_use_code "Usage (Bill To / Ship To)"  ,
  hcsua.attribute1 "Relation: PS US"               ,
  hcsua.TAX_REFERENCE "Tax Reg No Usage Level"     ,
  hcasa.attribute12 "Billing Setting Interface"    ,
  hcasa.attribute13 " Customer#"             ,
  hcasa.attribute2 "IBS BUC Code"                  ,
  hcasa.attribute1 "DUNS Number"                   ,
  hcp.contact_point_type "Phone Type"              ,
  hcp.phone_number "Phone Number"                 
  --  hps.party_site_id ,
  --  hps.location_id,
  --  hcasa.cust_acct_site_id
   FROM hz_parties hp          ,
  hz_cust_accounts hcas        ,
  hz_cust_acct_sites_all hcasa ,
  hz_cust_site_uses_all hcsua  ,
  hz_party_sites hps           ,
  hz_contact_points hcp        ,
  hz_locations hl
  WHERE hp.party_id         = hcas.party_id
AND hcas.cust_account_id    = hcasa.cust_account_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcsua.org_id            = hcasa.org_id
AND hp.party_id             = hps.party_id
AND hcasa.party_site_id     = hps.party_site_id
AND hcasa.status            = 'A'
AND HPS.STATUS              ='A'
AND hps.party_site_id       = hcp.owner_table_id(+)
AND hcp.contact_point_type  = 'PHONE'
AND hcp.OWNER_TABLE_NAME    = 'HZ_PARTY_SITES'
AND hcp.phone_line_type     = 'GEN'
AND hcp.status              = 'A'
AND hps.location_id         = hl.location_id
  --and hcas.account_number IN('13660','5856')
    --AND hcp.phone_number   IS NULL
AND hcasa.org_id IN (103,235,237,221);