CUSTOMER DUMP

SELECT DISTINCT
a.customer_name
,a.customer_name_phonetic alternate_name
,a.customer_number
,a.party_number
,ps.party_site_number site_number
,d.LOCATION
,a.orig_system_reference customer_ref
,b.orig_system_reference address_ref
,b.attribute3 site_classification
,b.attribute4 legacy_customer_num
,b.org_id
,hou.name circle_name
,d.SITE_USE_CODE
,d.PRIMARY_FLAG
,e.creation_date
,getlogin(e.created_by)
,ADDRESS1
,ADDRESS2
,ADDRESS3 ,
B.CITY,
b.STATE,
B.COUNTRY ,
B.POSTAL_CODE,
decode(a.STATUS,'A','Active','I','Inactive',b.STATUS) cust_header_status,
decode(b.STATUS,'A','Active','I','Inactive',b.STATUS) cust_site_status,
(select f.PARTY_NAME object
from HZ_RELATIONSHIPS_V f
where
   f.subject_id = a.party_id
   and rownum = 1) object,
d.SITE_USE_ID
FROM ra_customers a,
ra_addresses_all b,
hz_party_sites ps,
hz_cust_accounts_all c,
hz_cust_site_uses_all d,
hz_cust_acct_sites_all e,
hr_operating_units hou
--HZ_RELATIONSHIPS_V f
WHERE a.party_id=b.party_id
AND b.party_id=c.PARTY_ID
AND e.PARTY_SITE_ID=b.PARTY_SITE_ID
AND e.party_site_id=ps.party_site_id
AND d.org_id = hou.organization_id
AND e.cust_account_id = c.cust_account_id
AND e.cust_account_id = a.customer_id --------This code added by SIVA Reddy.
AND e.cust_acct_site_id = d.cust_acct_site_id
AND b.org_id=hou.ORGANIZATION_ID
--and nvl(a.party_id,1)  = nvl(f.subject_id,1)
AND b.org_id = 109
ORDER BY ps.party_site_number