To Get Receivables Ship To Query

SELECT DISTINCT hca.account_number, hps.party_site_number, hp.party_name,

                hl1.address1 street1, hl1.address2 street2,

                hl1.address3 street3, hl1.address4 street4,

                hl1.house_number house, hl1.city, hl1.postal_code,

                DECODE (hcp.phone_line_type,

                        'GEN', hcp.phone_area_code || hcp.phone_number,

                        NULL

                       ) phone_number,

                hl1.country, hl1.state region,

                DECODE (hcp.phone_line_type,

                        'FAX', hcp.phone_area_code || hcp.phone_number,

                        NULL

                       ) fax_number,

                hp.email_address

           FROM hz_parties hp,

                hz_locations hl1,

                hz_cust_site_uses_all hcsu,

                hz_cust_acct_sites_all hcas,

                hz_party_sites hps,

                hz_cust_accounts hca,

                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 hps.location_id = hl1.location_id

            AND hp.party_id = hcp.owner_table_id(+)

            AND hp.party_id = hps.party_id

            AND hca.cust_account_id = hcas.cust_account_id

            AND hcsu.site_use_code = 'SHIP_TO'

--            AND HP.PARTY_NAME LIKE 'A%'

ORDER BY        hp.party_name;