This Script will fetch the supplier contact details like first name, last name, email address, phone number, mobile number, fax number in Oracle R12 application
Main Table used in script
- hz_parties
- hz_party_sites
- hz_relationships
- ap_supplier_contacts
- hz_party_usg_assignments
SELECT hp.PERSON_TITLE,
hp.person_last_name ,
hp.person_first_name,
hp.PERSON_MIDDLE_NAME,
hcpe.email_address,
hcpp.phone_area_code,
hcpp.phone_number phone_number,
hcpf.phone_number fax_number,
hzr.end_date,
hzr.last_update_date
FROM hz_parties hp,
hz_party_sites hps,
hz_relationships hzr,
ap_supplier_contacts ascs,
hz_party_usg_assignments hpua,
hz_contact_points hcpp,
hz_contact_points hcpe,
hz_contact_points hcpf
WHERE 1 = 1
AND NVL (hps.end_date_active, SYSDATE) >= SYSDATE
-- and hps.party_id=39387
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.status = 'A'
AND hps.party_id = hzr.object_id
-- AND hps.party_site_id = p_supplier_party_site_id
AND hzr.relationship_id = ascs.relationship_id
AND hzr.party_id = ascs.rel_party_id
AND hps.party_site_id = ascs.org_party_site_id
AND hzr.subject_id = ascs.per_party_id
AND hp.party_id = hzr.subject_id
AND hpua.party_id = hp.party_id
AND hpua.status_flag IN ('A', 'I')
AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.owner_table_id(+) = hzr.party_id
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.primary_flag(+) = 'Y'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_id(+) = hzr.party_id
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.primary_flag(+) = 'Y'
AND (hcpe.status IS NULL OR hcpe.status IN ('A', 'I'))
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_id(+) = hzr.party_id
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND (hcpf.status IS NULL OR hcpf.status IN ('A', 'I'))
AND (hcpp.status IS NULL OR hcpp.status IN ('A', 'I'))
ORDER BY hzr.end_date DESC;