[Home] [Help]
| Product: | PV - Partner Management |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT JTF_GROUPS.GROUP_NAME SALES_GROUP_NAME
, JTF_GROUPS.GROUP_ID SALES_GROUP_ID
, REL_NAME.PERSON_FIRST_NAME||' '|| REL_NAME.PERSON_MIDDLE_NAME||' '|| REL_NAME.PERSON_LAST_NAME||' '|| REL_NAME.PERSON_NAME_SUFFIX SALESREP_FULL_NAME
, REL_NAME.PERSON_LAST_NAME SALESREP_LAST_NAME
, RELATIONSHIP.PARTY_ID EMPLOYEE_PERSON_ID
, RESOURCES.RESOURCE_ID SALESFORCE_ID
, PARTY.PARTY_NAME EMPLOYER_NAME
, REL_NAME.STATUS EMPLOYEE_STATUS
, REL_NAME.ADDRESS1 ||' '|| REL_NAME.ADDRESS2||' ' || REL_NAME.ADDRESS3||' '|| REL_NAME.ADDRESS4 ||' '|| REL_NAME.CITY||' '||REL_NAME.STATE||' '|| REL_NAME.PROVINCE||' '||REL_NAME.COUNTRY||' '||REL_NAME.POSTAL_CODE ADDRESS
, PARTY.PARTY_ID EMPLOYER_PARTY_ID
, FND_USER.USER_ID FND_USER_ID
, RESOURCES.RESOURCE_ID RESOURCE_ID
, 0 BUSINESS_GROUP_ID
, 0 SELECTABLE_FLAG
, RESOURCES.SOURCE_PHONE WORK_TELEPHONE
, FND_USER.EMAIL_ADDRESS EMAIL_ADDRESS
, FND_USER.USER_NAME USER_NAME
, REL_NAME.PARTY_NAME SALESPERSON_NAME
FROM HZ_PARTIES PARTY
, HZ_PARTY_RELATIONSHIPS RELATIONSHIP
, HZ_PARTIES REL_NAME
, HZ_ORG_CONTACTS ORG_CONTACT
, JTF_RS_RESOURCE_EXTNS RESOURCES
, FND_USER FND_USER
, JTF_RS_GROUPS_VL JTF_GROUPS
, JTF_RS_GROUP_MEMBERS GROUP_MEMBERS
WHERE PARTY.PARTY_ID = RELATIONSHIP.OBJECT_ID
AND RELATIONSHIP.PARTY_RELATIONSHIP_TYPE ='EMPLOYEE_OF'
AND RELATIONSHIP.SUBJECT_ID = REL_NAME.PARTY_ID
AND RELATIONSHIP.PARTY_RELATIONSHIP_ID = ORG_CONTACT.PARTY_RELATIONSHIP_ID
AND ORG_CONTACT.ORG_CONTACT_ID = RESOURCES.CONTACT_ID
AND RESOURCES.USER_ID = FND_USER.USER_ID
AND JTF_GROUPS.GROUP_ID = GROUP_MEMBERS.GROUP_ID
AND GROUP_MEMBERS.RESOURCE_ID = RESOURCES.RESOURCE_ID
AND GROUP_MEMBERS.DELETE_FLAG = 'N'
AND ( FND_USER.END_DATE IS NULL OR FND_USER.END_DATE > SYSDATE)