Product: | AHM - Hosting Manager(Obsolete) |
---|---|
Description: | This view gives following Subscriber Adminstrator and contact information that is captured at the time of Registration.1. User Name2. First Name3. Last Name4. Title5. Email Address6. Phone Number7. Party Numberetc. |
Implementation/DBA Data: | Not implemented in this database |
SELECT FU.USER_ID
, FU.USER_NAME
, HP.PERSON_FIRST_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_TITLE
, HCP2.EMAIL_ADDRESS
, HCP.PHONE_NUMBER
, HP.PARTY_ID
, HP.TOTAL_NUM_OF_ORDERS
, HP.VALIDATED_FLAG
, HP.PARTY_NUMBER
, HP.LAST_UPDATE_DATE PARTY_UPDATE_DATE
, HCP.LAST_UPDATE_DATE PHONE_UPDATE_DATE
, HCP2.LAST_UPDATE_DATE EMAIL_UPDATE_DATE
, HCP.CONTACT_POINT_ID PHONE_CONTACT_ID
, HCP2.CONTACT_POINT_ID EMAIL_CONTACT_ID
FROM HZ_PARTIES HP
, HZ_CONTACT_POINTS HCP
, HZ_CONTACT_POINTS HCP2
, HZ_RELATIONSHIPS HR
, FND_USER FU
WHERE FU.CUSTOMER_ID = HR.PARTY_ID
AND HR.SUBJECT_ID = HP.PARTY_ID
AND HR.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND HR.SUBJECT_ID = HCP.OWNER_TABLE_ID
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE = 'PHONE'
AND HCP.PHONE_LINE_TYPE = 'GEN'
AND HR.SUBJECT_ID = HCP2.OWNER_TABLE_ID
AND HCP2.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP2.CONTACT_POINT_TYPE = 'EMAIL' ORDER BY HP.PERSON_FIRST_NAME
, HP.PERSON_LAST_NAME