VIEW: APPS.PO_SUPPLIER_USERS_V

Source

View Text - Preformatted

SELECT fnd_user.user_id fnd_user_id, fnd_user.user_name fnd_user_name, fnd_user.customer_id fnd_customer_id, user_parties.party_id user_party_id, user_parties.party_name user_party_name, (user_parties.person_first_name || ' ' || decode(user_parties.person_middle_name, null, '', (user_parties.person_middle_name || ' ')) || user_parties.person_last_name) as user_party_full_name, hc_email.email_address user_party_email_address, user_parties.city user_party_city, user_parties.state user_party_state, user_parties.province user_party_province, user_parties.party_id vendor_party_id, po_vendors.vendor_name po_vendor_name, po_vendors.vendor_id po_vendor_id FROM fnd_user, hz_parties user_parties, po_vendors, hz_relationships employment_relationship, hz_contact_points hc_email WHERE fnd_user.person_party_id = user_parties.party_id AND employment_relationship.object_id = po_vendors.party_id AND employment_relationship.subject_id = user_parties.party_id AND employment_relationship.relationship_type = 'CONTACT' AND employment_relationship.relationship_code = 'CONTACT_OF' AND employment_relationship.object_type = 'ORGANIZATION' AND employment_relationship.object_table_name = 'HZ_PARTIES' AND employment_relationship.subject_type = 'PERSON' AND employment_relationship.subject_table_name = 'HZ_PARTIES' AND employment_relationship.status = 'A' AND hc_email.owner_table_name(+) = 'HZ_PARTIES' AND hc_email.owner_table_id(+) = employment_relationship.party_id AND hc_email.contact_point_type(+) = 'EMAIL' AND hc_email.EMAIL_FORMAT(+) = 'MAILTEXT' AND hc_email.status(+) = 'A' AND hc_email.primary_flag(+) = 'Y' AND Nvl(employment_relationship.end_date,SYSDATE)>=sysdate

View Text - HTML Formatted

SELECT FND_USER.USER_ID FND_USER_ID
, FND_USER.USER_NAME FND_USER_NAME
, FND_USER.CUSTOMER_ID FND_CUSTOMER_ID
, USER_PARTIES.PARTY_ID USER_PARTY_ID
, USER_PARTIES.PARTY_NAME USER_PARTY_NAME
, (USER_PARTIES.PERSON_FIRST_NAME || ' ' || DECODE(USER_PARTIES.PERSON_MIDDLE_NAME
, NULL
, ''
, (USER_PARTIES.PERSON_MIDDLE_NAME || ' ')) || USER_PARTIES.PERSON_LAST_NAME) AS USER_PARTY_FULL_NAME
, HC_EMAIL.EMAIL_ADDRESS USER_PARTY_EMAIL_ADDRESS
, USER_PARTIES.CITY USER_PARTY_CITY
, USER_PARTIES.STATE USER_PARTY_STATE
, USER_PARTIES.PROVINCE USER_PARTY_PROVINCE
, USER_PARTIES.PARTY_ID VENDOR_PARTY_ID
, PO_VENDORS.VENDOR_NAME PO_VENDOR_NAME
, PO_VENDORS.VENDOR_ID PO_VENDOR_ID
FROM FND_USER
, HZ_PARTIES USER_PARTIES
, PO_VENDORS
, HZ_RELATIONSHIPS EMPLOYMENT_RELATIONSHIP
, HZ_CONTACT_POINTS HC_EMAIL
WHERE FND_USER.PERSON_PARTY_ID = USER_PARTIES.PARTY_ID
AND EMPLOYMENT_RELATIONSHIP.OBJECT_ID = PO_VENDORS.PARTY_ID
AND EMPLOYMENT_RELATIONSHIP.SUBJECT_ID = USER_PARTIES.PARTY_ID
AND EMPLOYMENT_RELATIONSHIP.RELATIONSHIP_TYPE = 'CONTACT'
AND EMPLOYMENT_RELATIONSHIP.RELATIONSHIP_CODE = 'CONTACT_OF'
AND EMPLOYMENT_RELATIONSHIP.OBJECT_TYPE = 'ORGANIZATION'
AND EMPLOYMENT_RELATIONSHIP.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND EMPLOYMENT_RELATIONSHIP.SUBJECT_TYPE = 'PERSON'
AND EMPLOYMENT_RELATIONSHIP.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND EMPLOYMENT_RELATIONSHIP.STATUS = 'A'
AND HC_EMAIL.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HC_EMAIL.OWNER_TABLE_ID(+) = EMPLOYMENT_RELATIONSHIP.PARTY_ID
AND HC_EMAIL.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND HC_EMAIL.EMAIL_FORMAT(+) = 'MAILTEXT'
AND HC_EMAIL.STATUS(+) = 'A'
AND HC_EMAIL.PRIMARY_FLAG(+) = 'Y'
AND NVL(EMPLOYMENT_RELATIONSHIP.END_DATE
, SYSDATE)>=SYSDATE