View: AMS_PERSON_DETAILS_V

Product:	AMS - Marketing
Description:	Information about parties such as people.
Implementation/DBA Data:	APPS.AMS_PERSON_DETAILS_V

View Text

SELECT HP.PARTY_ID
, HP.PARTY_NUMBER
, HP.PARTY_NAME
, HP.PARTY_TYPE
, HP.STATUS
, HP.ORIG_SYSTEM_REFERENCE
, HP.CREATION_DATE
, HP.CREATED_BY
, HP.LAST_UPDATE_DATE
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_LOGIN
, HP.PROGRAM_UPDATE_DATE
, HP.WH_UPDATE_DATE
, HP.REQUEST_ID
, HP.PROGRAM_ID
, HP.PROGRAM_APPLICATION_ID
, HP.PERSON_IDENTIFIER
, HP.PERSON_IDEN_TYPE
, ' '
, HP.SALUTATION
, HP.PERSON_TITLE
, HP.PERSON_ACADEMIC_TITLE
, HP.PERSON_PRE_NAME_ADJUNCT
, HP.PERSON_FIRST_NAME
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_NAME_SUFFIX
, HP.PERSON_PREVIOUS_LAST_NAME
, HP.KNOWN_AS
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
, HP.PROVINCE
, HP.COUNTY
, HP.COUNTRY
, ' '
, HP.TAX_NAME
, HP.TAX_REFERENCE
, HP.CUSTOMER_KEY
, HP.GROUP_TYPE
, HP.VALIDATED_FLAG
, HP.SIC_CODE
, HP.SIC_CODE_TYPE
, HP.HQ_BRANCH_IND
, HP.JGZZ_FISCAL_CODE
, HP.DUNS_NUMBER
, HP.TOTAL_NUM_OF_ORDERS
, HP.TOTAL_ORDERED_AMOUNT
, HP.LAST_ORDERED_DATE
, HP.DO_NOT_MAIL_FLAG
, HP.REFERENCE_USE_FLAG
, HP.URL
, HP.EMAIL_ADDRESS
, HPP.PERSON_PROFILE_ID
, HPP.CONTENT_SOURCE_TYPE
, HPP.PERSON_NAME
, HPP.GENDER
, HPP.DECLARED_ETHNICITY
, TRUNC(MONTHS_BETWEEN(SYSDATE
, HPP.DATE_OF_BIRTH) / 12
, 0) "AGE"
, HPP.DATE_OF_BIRTH
, HPP.PLACE_OF_BIRTH
, HPP.DATE_OF_DEATH
, HPP.MARITAL_STATUS
, HPP.MARITAL_STATUS_EFFECTIVE_DATE
, HPP.PERSONAL_INCOME
, HPP.RENT_OWN_IND
, HPP.HEAD_OF_HOUSEHOLD_FLAG
, HPP.HOUSEHOLD_INCOME
, HPP.HOUSEHOLD_SIZE
, HPP.BEST_TIME_CONTACT_BEGIN
, HPP.BEST_TIME_CONTACT_END
, HPP.INTERNAL_FLAG
, HPS.PARTY_SITE_ID
, HPS.PARTY_SITE_NAME
, HPS.REGION
, HPS.MAILSTOP
, DECODE(HPL.LANGUAGE_NAME
, ''
, HL.LANGUAGE
, HPL.LANGUAGE_NAME) LANGUAGE
, HPS.IDENTIFYING_ADDRESS_FLAG
, HP.ATTRIBUTE_CATEGORY PARTY_ATTRIB_CATEGORY
, HP.ATTRIBUTE1 PARTY_ATTRIBUTE1
, HP.ATTRIBUTE2 PARTY_ATTRIBUTE2
, HP.ATTRIBUTE3 PARTY_ATTRIBUTE3
, HP.ATTRIBUTE4 PARTY_ATTRIBUTE4
, HP.ATTRIBUTE5 PARTY_ATTRIBUTE5
, HP.ATTRIBUTE6 PARTY_ATTRIBUTE6
, HP.ATTRIBUTE7 PARTY_ATTRIBUTE7
, HP.ATTRIBUTE8 PARTY_ATTRIBUTE8
, HP.ATTRIBUTE9 PARTY_ATTRIBUTE9
, HP.ATTRIBUTE10 PARTY_ATTRIBUTE10
, HP.ATTRIBUTE11 PARTY_ATTRIBUTE11
, HP.ATTRIBUTE12 PARTY_ATTRIBUTE12
, HP.ATTRIBUTE13 PARTY_ATTRIBUTE13
, HP.ATTRIBUTE14 PARTY_ATTRIBUTE14
, HP.ATTRIBUTE15 PARTY_ATTRIBUTE15
, HP.ATTRIBUTE16 PARTY_ATTRIBUTE16
, HP.ATTRIBUTE17 PARTY_ATTRIBUTE17
, HP.ATTRIBUTE18 PARTY_ATTRIBUTE18
, HP.ATTRIBUTE19 PARTY_ATTRIBUTE19
, HP.ATTRIBUTE20 PARTY_ATTRIBUTE20
, HP.ATTRIBUTE21 PARTY_ATTRIBUTE21
, HP.ATTRIBUTE22 PARTY_ATTRIBUTE22
, HP.ATTRIBUTE23 PARTY_ATTRIBUTE23
, HP.ATTRIBUTE24 PARTY_ATTRIBUTE24
, HPP.ATTRIBUTE_CATEGORY PROFILE_ATTRIB_CATEGORY
, HPP.ATTRIBUTE1 PROFILE_ATTRIBUTE1
, HPP.ATTRIBUTE2 PROFILE_ATTRIBUTE2
, HPP.ATTRIBUTE3 PROFILE_ATTRIBUTE3
, HPP.ATTRIBUTE4 PROFILE_ATTRIBUTE4
, HPP.ATTRIBUTE5 PROFILE_ATTRIBUTE5
, HPP.ATTRIBUTE6 PROFILE_ATTRIBUTE6
, HPP.ATTRIBUTE7 PROFILE_ATTRIBUTE7
, HPP.ATTRIBUTE8 PROFILE_ATTRIBUTE8
, HPP.ATTRIBUTE9 PROFILE_ATTRIBUTE9
, HPP.ATTRIBUTE10 PROFILE_ATTRIBUTE10
, HPP.ATTRIBUTE11 PROFILE_ATTRIBUTE11
, HPP.ATTRIBUTE12 PROFILE_ATTRIBUTE12
, HPP.ATTRIBUTE13 PROFILE_ATTRIBUTE13
, HPP.ATTRIBUTE14 PROFILE_ATTRIBUTE14
, HPP.ATTRIBUTE15 PROFILE_ATTRIBUTE15
, HPP.ATTRIBUTE16 PROFILE_ATTRIBUTE16
, HPP.ATTRIBUTE17 PROFILE_ATTRIBUTE17
, HPP.ATTRIBUTE18 PROFILE_ATTRIBUTE18
, HPP.ATTRIBUTE19 PROFILE_ATTRIBUTE19
, HPP.ATTRIBUTE20 PROFILE_ATTRIBUTE20
, HP.CREATED_BY CREATED_BY_NAME
, HP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, HP.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
FROM HZ_PERSON_PROFILES HPP
, HZ_PARTIES HP
, HZ_PARTY_SITES HPS
, HZ_PERSON_LANGUAGE HPL
, HZ_CONTACT_POINTS HCP
, HZ_LOCATIONS HL
WHERE HP.PARTY_TYPE = 'PERSON'
AND HP.STATUS = 'A'
AND HPS.LOCATION_ID = HL.LOCATION_ID (+)
AND HPP.ACTUAL_CONTENT_SOURCE = 'SST'
AND HP.PARTY_ID = HPP.PARTY_ID
AND HPP.PARTY_ID = HP.PARTY_ID
AND HPP.EFFECTIVE_END_DATE IS NULL
AND HPS.PARTY_ID(+) = HP.PARTY_ID
AND HPS.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED'
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HPL.PARTY_ID (+)= HP.PARTY_ID
AND HPL.NATIVE_LANGUAGE (+) = 'Y'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID (+) = HP.PARTY_ID
AND HCP.STATUS (+) = 'A'
AND HCP.PRIMARY_FLAG (+) = 'Y'

Columns