Supplier Contact
In this post, we will discuss on supplier contacts. Supplier contact includes persona name, contact like email, fax number, mobile number, address etc. which help organization to communicate with the supplier entity. I shared query which will provide you supplier details.
Detail Query
SELECT psv.vendor_name,
psv.vendor_id,
pssv.vendor_site_id,
pssv.party_site_name address_name,
pssv.address_line1
||','
||pssv.city
||' '
||pssv.zip
||','
||fnd.territory_short_name AS Address,
hou.name bu,
psav.party_site_name site,
psav.status,
psv.segment1 supplier_num,
psv.vendor_type_lookup_code supplier_type,
psv.vendor_name_alt alternate_name,
zxr.registration_number vendor_trn,
INITCAP(zxr.registration_status_code) tax_regi_status,
gcc.segment1
||'.'||
gcc.segment2
||'.'||
gcc.segment3
||'.'||
gcc.segment4
||'.'||
gcc.segment5
||'.'||
gcc.segment6 AS Liability_Account,
gcc1.segment1
||'.'||
gcc1.segment2
||'.'||
gcc1.segment3
||'.'||
gcc1.segment4
||'.'||
gcc1.segment5
||'.'||
gcc1.segment6 AS prepayment_account,
site_reg.registration_number site_regi_number,
INITCAP(site_reg.registration_status_code) site_status,
fnd.territory_short_name country_name,
(SELECT att.name
FROM ap_terms_b apt,
ap_terms_tl att
WHERE apt.term_id = att.term_id
AND att.language = 'US'
AND apt.term_id = pssv.terms_id
) payment_term,
sup_contact.first_name,
sup_contact.last_name,
sup_contact.email_address
FROM poz_suppliers_v psv,
poz_supplier_sites_v pssv,
poz_supplier_address_v psav,
poz_site_assignments_all_m pssam,
fnd_territories_vl fnd,
gl_code_combinations gcc,
gl_code_combinations gcc1,
hr_operating_units hou,
zx_party_tax_profile zptp,
zx_registrations zxr,
zx_party_tax_profile site_pro,
zx_registrations site_reg,
(SELECT poz_util.format_name(personparty.party_id) AS full_name,
personparty.person_first_name AS first_name,
personparty.person_last_name AS last_name,
email.email_address,
relationship.object_id
FROM hz_parties personparty,
hz_relationships relationship,
hz_party_usg_assignments partyusageassignment,
hz_org_contacts orgcontact,
hz_org_contact_roles orgcontactrole,
hz_contact_points email,
poz_lookup_codes statuslookup,
hz_lookups contacttitlelookup,
per_users users,
poz_supplier_contacts contactaddresses
WHERE personparty.status = 'A'
AND relationship.subject_id = personparty.party_id
AND relationship.relationship_type = 'CONTACT'
AND relationship.relationship_code = 'CONTACT_OF'
AND relationship.subject_type = 'PERSON'
AND relationship.subject_table_name = 'HZ_PARTIES'
AND relationship.object_type = 'ORGANIZATION'
AND relationship.object_table_name = 'HZ_PARTIES'
AND relationship.status = 'A'
AND orgcontact.party_relationship_id(+) = relationship.relationship_id
AND orgcontactrole.org_contact_id(+) = orgcontact.org_contact_id
AND orgcontactrole.role_type(+) = 'ADMINISTRATIVE_CONTACT_FOR'
AND orgcontactrole.status(+) = 'A'
AND personparty.party_id = partyusageassignment.party_id
AND partyusageassignment.party_usage_code = 'SUPPLIER_CONTACT'
AND partyusageassignment.status_flag = 'A'
AND partyusageassignment.owner_table_id = relationship.relationship_id
AND partyusageassignment.owner_table_name = 'HZ_RELATIONSHIPS'
AND email.owner_table_name(+) = 'HZ_PARTIES'
AND email.owner_table_id(+) = relationship.subject_id
and email.relationship_id(+) = relationship.relationship_id
AND email.contact_point_type(+) = 'EMAIL'
AND email.primary_flag(+) = 'Y'
AND email.status(+) = 'A'
AND statuslookup.lookup_type(+) = 'POZ_STATUS'
AND statuslookup.lookup_code(+) = (CASE WHEN SYSDATE < NVL(partyusageassignment.effective_end_date, SYSDATE + 1)
THEN 'ACTIVE'
ELSE 'INACTIVE'
END
)
AND statuslookup.enabled_flag(+) = 'Y'
AND contacttitlelookup.lookup_type(+) = 'CONTACT_TITLE'
AND contacttitlelookup.lookup_code(+) = personparty.person_pre_name_adjunct
AND contacttitlelookup.enabled_flag(+) = 'Y'
AND personparty.party_id = users.party_id(+)
AND contactaddresses.per_party_id = personparty.party_id
) sup_contact
WHERE 1 = 1
AND psv.vendor_id = pssv.vendor_id(+)
AND hou.organization_id = pssv.prc_bu_id
AND psav.party_site_id = pssv.party_site_id
AND psav.vendor_id(+) = psv.vendor_id
AND pssam.vendor_site_id(+) = pssv.vendor_site_id
AND pssv.country(+) = fnd.territory_code
AND gcc.code_combination_id(+) = pssam.accts_pay_code_combination_id
AND gcc1.code_combination_id(+) = pssam.prepay_code_combination_id
AND zptp.party_id(+) = psv.party_id
AND zptp.party_tax_profile_id = zxr.party_tax_profile_id(+)
AND site_pro.party_id(+) = pssv.party_site_id
AND site_pro.party_tax_profile_id = site_reg.party_tax_profile_id(+)
AND psv.party_id = sup_contact.OBJECT_ID(+)
AND NVL(hou.organization_id, -999) = NVL(:p_bu_name, NVL(hou.organization_id,-999))
AND psv.vendor_id = NVL(:p_supp_number, psv.vendor_id)
ORDER BY psv.vendor_name,pssv.party_site_name
psv.vendor_id,
pssv.vendor_site_id,
pssv.party_site_name address_name,
pssv.address_line1
||','
||pssv.city
||' '
||pssv.zip
||','
||fnd.territory_short_name AS Address,
hou.name bu,
psav.party_site_name site,
psav.status,
psv.segment1 supplier_num,
psv.vendor_type_lookup_code supplier_type,
psv.vendor_name_alt alternate_name,
zxr.registration_number vendor_trn,
INITCAP(zxr.registration_status_code) tax_regi_status,
gcc.segment1
||'.'||
gcc.segment2
||'.'||
gcc.segment3
||'.'||
gcc.segment4
||'.'||
gcc.segment5
||'.'||
gcc.segment6 AS Liability_Account,
gcc1.segment1
||'.'||
gcc1.segment2
||'.'||
gcc1.segment3
||'.'||
gcc1.segment4
||'.'||
gcc1.segment5
||'.'||
gcc1.segment6 AS prepayment_account,
site_reg.registration_number site_regi_number,
INITCAP(site_reg.registration_status_code) site_status,
fnd.territory_short_name country_name,
(SELECT att.name
FROM ap_terms_b apt,
ap_terms_tl att
WHERE apt.term_id = att.term_id
AND att.language = 'US'
AND apt.term_id = pssv.terms_id
) payment_term,
sup_contact.first_name,
sup_contact.last_name,
sup_contact.email_address
FROM poz_suppliers_v psv,
poz_supplier_sites_v pssv,
poz_supplier_address_v psav,
poz_site_assignments_all_m pssam,
fnd_territories_vl fnd,
gl_code_combinations gcc,
gl_code_combinations gcc1,
hr_operating_units hou,
zx_party_tax_profile zptp,
zx_registrations zxr,
zx_party_tax_profile site_pro,
zx_registrations site_reg,
(SELECT poz_util.format_name(personparty.party_id) AS full_name,
personparty.person_first_name AS first_name,
personparty.person_last_name AS last_name,
email.email_address,
relationship.object_id
FROM hz_parties personparty,
hz_relationships relationship,
hz_party_usg_assignments partyusageassignment,
hz_org_contacts orgcontact,
hz_org_contact_roles orgcontactrole,
hz_contact_points email,
poz_lookup_codes statuslookup,
hz_lookups contacttitlelookup,
per_users users,
poz_supplier_contacts contactaddresses
WHERE personparty.status = 'A'
AND relationship.subject_id = personparty.party_id
AND relationship.relationship_type = 'CONTACT'
AND relationship.relationship_code = 'CONTACT_OF'
AND relationship.subject_type = 'PERSON'
AND relationship.subject_table_name = 'HZ_PARTIES'
AND relationship.object_type = 'ORGANIZATION'
AND relationship.object_table_name = 'HZ_PARTIES'
AND relationship.status = 'A'
AND orgcontact.party_relationship_id(+) = relationship.relationship_id
AND orgcontactrole.org_contact_id(+) = orgcontact.org_contact_id
AND orgcontactrole.role_type(+) = 'ADMINISTRATIVE_CONTACT_FOR'
AND orgcontactrole.status(+) = 'A'
AND personparty.party_id = partyusageassignment.party_id
AND partyusageassignment.party_usage_code = 'SUPPLIER_CONTACT'
AND partyusageassignment.status_flag = 'A'
AND partyusageassignment.owner_table_id = relationship.relationship_id
AND partyusageassignment.owner_table_name = 'HZ_RELATIONSHIPS'
AND email.owner_table_name(+) = 'HZ_PARTIES'
AND email.owner_table_id(+) = relationship.subject_id
and email.relationship_id(+) = relationship.relationship_id
AND email.contact_point_type(+) = 'EMAIL'
AND email.primary_flag(+) = 'Y'
AND email.status(+) = 'A'
AND statuslookup.lookup_type(+) = 'POZ_STATUS'
AND statuslookup.lookup_code(+) = (CASE WHEN SYSDATE < NVL(partyusageassignment.effective_end_date, SYSDATE + 1)
THEN 'ACTIVE'
ELSE 'INACTIVE'
END
)
AND statuslookup.enabled_flag(+) = 'Y'
AND contacttitlelookup.lookup_type(+) = 'CONTACT_TITLE'
AND contacttitlelookup.lookup_code(+) = personparty.person_pre_name_adjunct
AND contacttitlelookup.enabled_flag(+) = 'Y'
AND personparty.party_id = users.party_id(+)
AND contactaddresses.per_party_id = personparty.party_id
) sup_contact
WHERE 1 = 1
AND psv.vendor_id = pssv.vendor_id(+)
AND hou.organization_id = pssv.prc_bu_id
AND psav.party_site_id = pssv.party_site_id
AND psav.vendor_id(+) = psv.vendor_id
AND pssam.vendor_site_id(+) = pssv.vendor_site_id
AND pssv.country(+) = fnd.territory_code
AND gcc.code_combination_id(+) = pssam.accts_pay_code_combination_id
AND gcc1.code_combination_id(+) = pssam.prepay_code_combination_id
AND zptp.party_id(+) = psv.party_id
AND zptp.party_tax_profile_id = zxr.party_tax_profile_id(+)
AND site_pro.party_id(+) = pssv.party_site_id
AND site_pro.party_tax_profile_id = site_reg.party_tax_profile_id(+)
AND psv.party_id = sup_contact.OBJECT_ID(+)
AND NVL(hou.organization_id, -999) = NVL(:p_bu_name, NVL(hou.organization_id,-999))
AND psv.vendor_id = NVL(:p_supp_number, psv.vendor_id)
ORDER BY psv.vendor_name,pssv.party_site_name