Supplier contact query in Oracle fusion

Supplier contact query in Oracle fusion

Hi friends , We are going to discuss about Supplier contact query in Oracle Fusion. We are sharing the detail sql query to extract the supplier contacts in oracle fusion. Using this sql query , we can develop the Custom BIP report for supplier contacts in oracle fusion. This sql query will also include the Supplier addresses including the supplier contacts details in oracle fusion. This is one of the important SQL query related to suppliers in oracle Fusion. This will give us the complete details about supplier contacts in Oracle Fusion. Please find below the Complete detail about Supplier contact query in Oracle fusion.

Supplier Contact Query in Oracle fusion

10 Important Table in Supplier contact query

1.HZ_PARTIES

2.HZ_RELATIONSHIPS

3.HZ_PARTY_USG_ASSIGNMENTS

4.HZ_ORG_CONTACTS

5.HZ_ORG_CONTACT_ROLES

6.HZ_CONTACT_POINTS

7.POZ_LOOKUP_CODES

8.HZ_LOOKUPS

9.PER_USERS

10.POZ_SUPPLIER_CONTACTS

Details Supplier contact query in Oracle fusion

Here below is the details SQL Query which helps to extract the supplier contacts details in Oracle Fusion.

SELECT

PersonParty.PARTY_ID as PERSON_PARTY_ID,

poz_util.format_name(PersonParty.party_id) as FULL_NAME,

DECODE(Users.USER_GUID, null, 'N', 'Y') as HAS_USER_ACCOUNT,

PersonParty.PERSON_PRE_NAME_ADJUNCT as CONTACT_TITLE,

PersonParty.PERSON_FIRST_NAME as FIRST_NAME,

PersonParty.PERSON_MIDDLE_NAME as MIDDLE_NAME,

PersonParty.PERSON_LAST_NAME as LAST_NAME,

PersonParty.CREATED_BY,

PersonParty.CREATION_DATE,

PersonParty.LAST_UPDATED_BY,

PersonParty.LAST_UPDATE_DATE,

Relationship.RELATIONSHIP_ID,

Relationship.OBJECT_ID as SUPPLIER_PARTY_ID,

(CASE WHEN trunc(PartyUsageAssignment.EFFECTIVE_END_DATE)=to_date('31-12-4712','DD-MM-YYYY') THEN NULL ELSE PartyUsageAssignment.EFFECTIVE_END_DATE END) as END_DATE,

OrgContact.JOB_TITLE,

DECODE(OrgContactRole.ROLE_TYPE, null, 'N', 'Y') as ADMINISTRATIVE_CONTACT,

Email.EMAIL_ADDRESS,

Phone.PHONE_COUNTRY_CODE,

Phone.PHONE_AREA_CODE,

Phone.PHONE_NUMBER,

Phone.PHONE_EXTENSION,

Mobile.PHONE_COUNTRY_CODE as MOBILE_COUNTRY_CODE,

Mobile.PHONE_AREA_CODE as MOBILE_AREA_CODE,

Mobile.PHONE_NUMBER as MOBILE_NUMBER,

NVL2(Mobile.CONTACT_POINT_ID,'Y','N') as MOBILE_EXISTS,

Fax.PHONE_COUNTRY_CODE as FAX_COUNTRY_CODE,

Fax.PHONE_AREA_CODE as FAX_AREA_CODE,

Fax.PHONE_NUMBER as FAX_NUMBER,

StatusLookup.LOOKUP_CODE as CONTACT_STATUS,

StatusLookup.DISPLAYED_FIELD as CONTACT_STATUS_DISPLAY,

null FULL_PHONE,

null FULL_MOBILE,

null FULL_FAX,

ContactTitleLookup.meaning as ContactTitleDisplayed,

decode(Users.user_guid,null,'N','Y') user_account,

poz_util.format_name(PersonParty.party_id,'CONCAT_NAME') as DISPLAY_NAME,

ContactAddresses.PARTY_SITE_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,

HZ_CONTACT_POINTS Phone,

HZ_CONTACT_POINTS Mobile,

HZ_CONTACT_POINTS Fax,

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 Phone.RELATIONSHIP_ID(+) = Relationship.RELATIONSHIP_ID

and Phone.CONTACT_POINT_TYPE(+) = 'PHONE'

and Phone.PHONE_LINE_TYPE(+) = 'GEN'

and Phone.STATUS(+) = 'A'

and Phone.PRIMARY_FLAG(+) = 'Y'

AND Mobile.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'

and Mobile.OWNER_TABLE_ID(+) = Relationship.SUBJECT_ID

and Mobile.RELATIONSHIP_ID(+) = Relationship.RELATIONSHIP_ID

and Email.STATUS(+) = 'A'

AND Phone.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'

and Phone.OWNER_TABLE_ID(+) = Relationship.SUBJECT_ID

and Mobile.CONTACT_POINT_TYPE(+) = 'PHONE'

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

and Mobile.PHONE_LINE_TYPE(+) = 'MOBILE'

and Mobile.STATUS(+) = 'A'

AND Fax.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'

and Fax.OWNER_TABLE_ID(+) = Relationship.SUBJECT_ID

and Fax.RELATIONSHIP_ID(+) = Relationship.RELATIONSHIP_ID

and Fax.CONTACT_POINT_TYPE(+) = 'PHONE'

and Fax.PHONE_LINE_TYPE(+) = 'FAX'

and Fax.STATUS(+) = 'A'

and Relationship.OBJECT_ID=(select party_id from POZ_SUPPLIERS_V where POZ_SUPPLIERS_V.vendor_name like '<Supplier Name>')

Supplier contact query in Oracle fusion