In this post you’ll see SQL query to extract Supplier Contact information in Oracle ERP Fusion Application, this SQL is having some important columns link supplier Supplier name, first name, last name, job title, email, phone number, fax number, mobile number, I hope this query will help you to build BIP report to extract supplier contact information from Oracle Fusion Application.
SELECT
ASA.vendor_name “Supplier_Name”
, PERSON.person_pre_name_adjunct “Prefix”
, PERSON.person_first_name “First_Name”
, PERSON.person_middle_name “Middle_Name”
, PERSON.person_last_name “Last_Name”
, PERSON.person_title “Job_Title”
, HCPE.email_address “E-Mail”
, HCPP.phone_country_code “Phone_Country_Code”
, HCPP.phone_area_code “Phone_Area_Code”
, HCPP.phone_number “Phone”
, HCPP.phone_extension “Phone_Extension”
, HCPF.phone_country_code “Fax_Country_Code”
, HCPF.phone_area_code “Fax_Area_Code”
, HCPF.phone_number “Fax”
, HCPM.phone_country_code “Mobile_Country_Code”
, HCPM.phone_area_code “Mobile_Area_Code”
, HCPM.phone_number “Mobile”
FROM
Poz_Suppliers_v ASA,
Hz_Parties HP,
Hz_Parties PERSON,
Hz_Relationships Rel,
Poz_Lookup_Codes StatusLookup,
Hz_Contact_Points HCPP,
Hz_Contact_Points HCPF,
Hz_Contact_Points HCPE,
HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment,
Hz_Contact_points HCPM,
Hz_Person_Profiles HPP
WHERE
HP.Party_Id = ASA.Party_Id
AND PERSON.Status = ‘A’
AND PERSON.Party_Type =’PERSON’
AND HP.Party_Type =’ORGANIZATION’
AND (Rel.Subject_Id = PERSON.Party_Id
AND Rel.Object_Id = HP.Party_Id
AND Rel.Relationship_Type = ‘CONTACT’
AND Rel.relationship_code = ‘CONTACT_OF’
AND Rel.SUBJECT_TYPE = ‘PERSON’
AND Rel.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’
AND Rel.OBJECT_TYPE = ‘ORGANIZATION’
AND Rel.OBJECT_TABLE_NAME = ‘HZ_PARTIES’
AND Rel.STATUS = ‘A’)
AND StatusLookup.lookup_type(+) = ‘POZ_STATUS’
AND (CASE WHEN sysdate >= NVL(PartyUsageAssignment.EFFECTIVE_START_DATE, sysdate-1)
AND sysdate < NVL(PartyUsageAssignment.EFFECTIVE_END_DATE, sysdate +1)
THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END) = StatusLookup.lookup_code(+)
AND (HCPP.Owner_Table_id(+) = Rel.Subject_id
AND HCPP.Relationship_id(+) = Rel.Relationship_id
AND HCPP.Owner_Table_Name(+) = ‘HZ_PARTIES’
AND HCPP.Contact_Point_Type(+) = ‘PHONE’
AND HCPP.Phone_Line_Type(+) = ‘GEN’
AND HCPP.Status(+) = ‘A’
AND HCPP.Primary_Flag(+) = ‘Y’)
AND (HCPF.Owner_Table_id(+) = Rel.Subject_id
AND HCPF.Relationship_id(+) = Rel.Relationship_id
AND HCPF.Owner_Table_Name(+) = ‘HZ_PARTIES’
AND HCPF.Contact_Point_Type(+) = ‘PHONE’
AND HCPF.Phone_Line_Type(+) = ‘FAX’
AND HCPF.Status(+) = ‘A’)
AND (HCPM.Owner_Table_id(+) = Rel.Subject_id
AND HCPM.Relationship_id(+) = Rel.Relationship_id
AND HCPM.Owner_Table_Name(+) = ‘HZ_PARTIES’
AND HCPM.Contact_Point_Type(+) = ‘PHONE’
AND HCPM.Phone_Line_Type(+) = ‘MOBILE’
AND HCPM.Status(+) = ‘A’)
AND (PERSON.PARTY_ID = PartyUsageAssignment.PARTY_ID
AND PartyUsageAssignment.PARTY_USAGE_CODE = ‘SUPPLIER_CONTACT’
AND PartyUsageAssignment.STATUS_FLAG = ‘A’
AND PartyUsageAssignment.OWNER_TABLE_ID = Rel.RELATIONSHIP_ID
AND PartyUsageAssignment.OWNER_TABLE_NAME = ‘HZ_RELATIONSHIPS’)
AND (HCPE.Owner_Table_id(+) = Rel.Subject_id
AND HCPE.Relationship_id(+) = Rel.Relationship_id
AND HCPE.Owner_Table_Name(+) = ‘HZ_PARTIES’
AND HCPE.Contact_Point_Type(+) = ‘EMAIL’
AND HCPE.Status(+) = ‘A’
AND HCPE.Primary_Flag(+) = ‘Y’)
and Rel.Subject_Id = HPP.Party_Id