Customer Site Information - Basic

SELECT a.PARTY_NAME,
       a.PARTY_ID,
       a.PARTY_NUMBER,
       a.PARTY_TYPE,
       a.ORIG_SYSTEM_REFERENCE,
       a.COUNTRY,
       a.ADDRESS1 ||','|| CITY ||','|| POSTAL_CODE ||','|| STATE AS "ACCOUNT ADDRESS",
       a.STATUS,
       a.IDEN_ADDR_PARTY_SITE_ID,
       a.IDEN_ADDR_LOCATION_ID,
       b.ACCOUNT_NAME,
       c.CUST_ACCT_SITE_ID,
       c.CUST_ACCOUNT_ID,
       c.PARTY_SITE_ID,
       c.ORIG_SYSTEM_REFERENCE AS "Cust Acct Site Origin REF",
       c.STATUS AS "Cust Acct Site Status",
       c.SET_ID AS "Cust Acct Site Set ID",
       c.BILL_TO_FLAG,
       c.MARKET_FLAG,
       c.SHIP_TO_FLAG,
       d.ORIG_SYSTEM_REFERENCE AS "Cust Acct Site Use Origin REF",
       d.STATUS AS "Cust Acct Site Use Status",
       d.SET_ID,
       d.SITE_USE_ID,
       d.SITE_USE_CODE,
       d.PRIMARY_FLAG,
       d.LOCATION,
       d.BILL_TO_SITE_USE_ID
FROM hz_parties a,
     hz_cust_accounts b,
     hz_cust_acct_sites_all c,
     hz_cust_site_uses_all d
WHERE (upper(a.party_name) LIKE upper('Comp%')
       OR a.party_id IN (300000005352190, 300000001469001, 300000005352164))
  AND a.party_id = b.party_id(+)
  AND c.cust_account_id = b.cust_account_id
  AND c.cust_acct_site_id = d.cust_acct_site_id
ORDER BY a.PARTY_NAME;