SQL Query to get the Supplier Contact Details - Oracle Fusion.

 Here is the generic SQL Query to get the Supplier Contact details as per the FBDI template in Oracle fusion.

select 

hpo.party_name  supplier_name,

hpp.person_pre_name_adjunct,

hpp.person_first_name,

hpp.person_middle_name,

hpp.person_last_name,

hc.job_title,

hps.party_site_name,

hor.role_type,

email.email_address,

phone.phone_country_code,

phone.phone_area_code,

phone.phone_number,

phone.phone_extension,

fax.fax_country_code,

fax.fax_area_code,

fax.fax,

mobile.mobile_country_code,

mobile.mobile_area_code,

mobile.mobile,

poc.inactive_date

FROM

hz_parties hpo,

hz_relationships hzr,

hz_parties hpp,

hz_org_contacts hc,

hz_org_contact_roles hor,

poz_supplier_contacts poc,

hz_party_sites hps,

poz_suppliers ps,

(select email_address, owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='email'

and hcp.owner_table_name='hz_parties') email,

(select phone_country_code,phone_area_code,phone_number,phone_extension,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'gen') phone,

(select phone_country_code fax_country_code,phone_area_code fax_area_code,phone_number fax,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'fax') fax,

(select phone_country_code mobile_country_code,phone_area_code mobile_area_code, phone_number mobile,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'mobile') mobile

where

hpo.party_type='organization'

and hzr.object_id=hpo.party_id

and hpp.party_type='person'

and hpp.party_id=hzr.subject_id

and hc.party_relationship_id=hzr.relationship_id

and hps.party_site_id(+) =  poc.party_site_id

and hzr.relationship_id = poc.relationship_id(+)

and hor.org_contact_id(+) = hc.org_contact_id

and ps.party_id=hpo.party_id

and email.owner_table_id(+)=hpp.party_id

and phone.owner_table_id(+)=hpp.party_id

and fax.owner_table_id(+)=hpp.party_id

and mobile.owner_table_id(+)=hpp.party_id

and hzr.relationship_code = 'contact_of'