In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex with integrating suppliers into TCA architecture. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete.
In R12 for every supplier contact a party and a party relationship is created in Trading-Community Architecture.
Following query fetches details on Supplier site contacts:-
-- Search Supplier Contact
SELECT DISTINCT asu.party_id, asu.segment1 Supp_Num
,asu.vendor_name
,hpc.party_name Contact_Name
,hpr.primary_phone_country_code cnt_cntry
,hpr.primary_phone_area_code cnt_area
,hpr.primary_phone_number cnt_phone
,assa.vendor_site_code
,assa.vendor_site_id
,asco.vendor_contact_id
FROM
hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES';
Thanks.
ReplyDeleteThanks for the query. I created a contact from Oracle rel 12 Supplier Screen-> contact. This query is not showing it up. It created a contact in hz_parties becase I see a new record is created there. But how do I join it ,, it does not seem to go thru path above. If I take out hoc.party_relationship_id = hr.relationship_id join it gives me 2 rows for the contact that I create?
ReplyDeleteWhat am I missing?
I figured this out. The above query only give contacts if there is site associated with it. This is important eg. when you create a PO.
ReplyDeleteBut it is possible to create a contact that does not have any site associated with it. The above query will not return that. Here is the query that will return all contacts associated with the supplier.
SELECT DISTINCT
asco.vendor_contact_id entityHandle,
hpr.party_number entityId,
hpc.person_title title,
hpc. salutation,
hpc.PERSON_FIRST_NAME firstName,
hpc.PERSON_MIDDLE_NAME middleName,
hpc.PERSON_LAST_NAME lastName,
asu.vendor_id companyHandle,
'FALSE' isPrivate,
hpr.EMAIL_ADDRESS email,
asu.vendor_name address1_addressee,
hpr.ADDRESS1 address1_line1,
hpr.ADDRESS2 address1_line2,
hpr.city address1_city,
hpr.state address1_state,
hpr.postal_code address1_zipCode,
hpr.country address1_country
FROM
hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES'
UNION
SELECT DISTINCT
pvc.vendor_contact_id entityHandle,
hp2.party_number entityId,
hp.person_title title,
hp. salutation,
hp.PERSON_FIRST_NAME firstName,
hp.PERSON_MIDDLE_NAME middleName,
hp.PERSON_LAST_NAME lastName,
aps.vendor_id companyHandle, -- link to vendor template entityHandle
'FALSE' isPrivate,
hp2.EMAIL_ADDRESS email,
aps.vendor_name address1_addressee,
hp2.ADDRESS1 address1_line1,
hp2.ADDRESS2 address1_line2,
hp2.city address1_city,
hp2.state address1_state,
hp2.postal_code address1_zipCode,
hp2.country address1_country
FROM AP_SUPPLIER_CONTACTS PVC,
HZ_PARTIES HP,
HZ_RELATIONSHIPS HPR,
HZ_ORG_CONTACTS HOC,
HZ_PARTIES HP2,
AP_SUPPLIERS APS
WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
AND PVC.REL_PARTY_ID = HP2.PARTY_ID
AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
AND HPR.DIRECTIONAL_FLAG = 'F'
AND PVC.ORG_PARTY_SITE_ID IS NULL
AND PVC.VENDOR_SITE_ID IS NULL
AND HPR.OBJECT_ID = APS.PARTY_ID
AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND HPR.OBJECT_TYPE = 'ORGANIZATION'
AND NVL (APS.VENDOR_TYPE_LOOKUP_CODE, 'DUMMY') <> 'EMPLOYEE'
I faced the same issue today and the below query has worked, please try this, hope it will work for u guys.
ReplyDeleteselect distinct b.party_id,
aps.vendor_id,
aps.segment1 supplier_num,
aps.VENDOR_NAME supplier_name,
a.SUBJECT_PARTY_NAME party_name,
b.EMAIL_ADDRESS,
hcpp.phone_number,
hcpf.phone_number fax_number,
b.EMAIL_ADDRESS user_name,
trunc(a.end_date) end_date
from apps.HZ_PARTY_RELATIONSHIP_V a,
apps.hz_parties b,
apps.ap_suppliers aps,
apps.po_headers_all pha,
apps.per_people_x papf,
apps.hz_contact_points hcpp,
apps.hz_contact_points hcpf
where 1=1 --and a.object_id=10042
and a.PARTY_ID = b.party_id
and a.object_id = aps.party_id
and pha.agent_id = papf.person_id
AND aps.vendor_id = pha.vendor_id
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.owner_table_id(+) = a.party_id
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_id(+) = a.party_id
AND hcpf.phone_line_type(+) = 'FAX'
AND hcpf.contact_point_type(+) = 'PHONE'
Nice query
DeleteThank you
This comment has been removed by the author.
DeleteCool. I had modified to suit my requirements:
Deleteselect distinct b.party_id,
aps.vendor_id,
aps.segment1 supplier_num,
aps.VENDOR_NAME supplier_name,
a.SUBJECT_PARTY_NAME CONTACT_NAME,
b.EMAIL_ADDRESS,
hcpp.phone_number,
hcpf.phone_number fax_number,
b.EMAIL_ADDRESS user_name,
trunc(a.end_date) end_date
from apps.HZ_PARTY_RELATIONSHIP_V a,
apps.hz_parties b,
apps.ap_suppliers aps,
apps.po_headers_all pha,
apps.per_people_x papf,
apps.hz_contact_points hcpp,
apps.hz_contact_points hcpf,
apps.hz_contact_points hcpg
where 1=1 --and a.object_id=10042
and a.PARTY_ID = b.party_id
and a.object_id = aps.party_id
and pha.agent_id = papf.person_id
AND aps.vendor_id = pha.vendor_id
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.owner_table_id(+) = a.party_id
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_id(+) = a.party_id
AND hcpf.phone_line_type(+) = 'FAX'
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpg.owner_table_name(+) = 'HZ_PARTIES'
AND hcpg.owner_table_id(+) = a.party_id
AND hcpg.contact_point_type(+) = 'EMAIL'
AND vendor_name = &SUPPLIER
how to join apps.HZ_PARTY_RELATIONSHIP_V and hz_contact_points table.
ReplyDelete