eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AMS_PERSON_DETAILS_V

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'