--------------------------------Customer:
select *
FROM hz_parties party, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
---------------------------------Site:
select *
from apps.hz_cust_site_uses_all hs
, hz_cust_acct_sites_all cs
, hz_party_sites ps
, hz_locations hl
, HZ_CUSTOMER_PROFILES p
, ra_terms ter
where hs.cust_acct_site_id=cs.cust_acct_site_id
and cs.party_site_id=ps.party_site_id
and ps.location_id= hl.location_id(+)
and cs.CUST_ACCOUNT_ID = p.cust_account_id (+)
and p.standard_terms=ter.term_id(+);
----------------------------------Contact:
select p.party_id,c.contact_id account_conact_id
, c.contact_name
, c.contact_number
, a.PHONE_LINE_TYPE
, a.PHONE_COUNTRY_CODE
, a.PHONE_AREA_CODE
, a.PHONE_NUMBER
, a.PHONE_EXTENSION
, e.email_address
fromhz_parties p
,(select cp.owner_table_id party_id,contact_point_type
, cp.PHONE_LINE_TYPE ,cp.PHONE_COUNTRY_CODE ,cp.PHONE_AREA_CODE
, cp.PHONE_NUMBER, cp.PHONE_EXTENSION
from hz_contact_points cp
where contact_point_type in ('PHONE')
and owner_table_name = 'HZ_PARTIES') a
,(select cp.owner_table_id party_id,contact_point_type,
cp.email_address
from hz_contact_points cp
where contact_point_type in ('EMAIL')
and owner_table_name = 'HZ_PARTIES') e
, (select r.object_id party_id, r.party_id contact_id
, p.party_name contact_name
, o.contact_number
from hz_relationships r ,HZ_PARTIES p, HZ_ORG_CONTACTS o
where relationship_code = 'CONTACT_OF'
and r.subject_id=p.party_id
and r.relationship_id = o.party_relationship_id) c
where1=1 --p.party_type = 'ORGANIZATION'
and p.party_id=a.party_id(+)
and p.party_id=c.party_id(+)
and p.party_id=e.party_id(+)
;
|
