TCA (Queries)

Trading Community Architecture

1- In order to get Phone number of supplier/customer site the query will be (HZ Tables)

Select hz.party_id, hz.party_name, hps.party_site_name, hcp.phone_number

from      hz_contact_points hcp,

    hz_party_sites hps,

hz_parties hz

where 

hz.party_id=77042 and

hz.party_id = hps.party_id(+) and

hps.party_site_id = hcp.owner_table_id(+) and

hcp.owner_table_name='HZ_PARTY_SITES' and
 hz.application_id=200;

* If you replace application_id to 222 then you can get the detail of Customer

2- In order to get Phone number of supplier/customer  the query will be (HZ Tables)

Select hz.party_id, hz.party_name,  hcp.phone_number

from   hz_contact_points hcp,

hz_parties hz,

hz_relationships hr

where 

hz.party_id=77042 and

    hz.party_id = hr.object_id(+) and

    hr.party_id = hcp.owner_table_id(+) and

hcp.owner_table_name='HZ_PARTIES' and
 hz.application_id=200;

* If you replace application_id to 222 then you can get the detail of Customer

3- Query to get email address of supplier

select email.email_address
from
hz_party_sites hps,
hz_contact_points email
where
hps.party_id=(select party_id from ap_suppliers where vendor_id=1330)
--9178 and
and
email.owner_table_id(+) = hps.party_site_id and
email.owner_table_name(+) = 'HZ_PARTY_SITES' and
email.status(+) = 'A' and
email.contact_point_type(+) = 'EMAIL' and

email.primary_flag(+) = 'Y' ;