VIEW: APPS.AMS_PERSON_SITES_V

Source

View Text - Preformatted

SELECT hps.PARTY_SITE_ID ,hps.PARTY_ID ,hps.LOCATION_ID ,hps.PARTY_SITE_NAME ,hps.PARTY_SITE_NUMBER ,hps.IDENTIFYING_ADDRESS_FLAG ,hps.REGION ,hps.MAILSTOP ,hl.LANGUAGE LANGUAGE ,hps.ADDRESSEE ,hps.STATUS ,hcpref.PREFERENCE_START_DATE ,hcpref.PREFERENCE_START_TIME_HR ,hcpref.PREFERENCE_START_TIME_MI ,hcpref.PREFERENCE_END_DATE ,hcpref.PREFERENCE_END_TIME_HR ,hcpref.PREFERENCE_END_TIME_MI FROM HZ_PARTY_SITES hps, HZ_PARTIES hp, HZ_CONTACT_PREFERENCES hcpref, HZ_LOCATIONS hl WHERE hps.STATUS = 'A' AND hps.PARTY_ID = hp.PARTY_ID AND hps.LOCATION_ID = HL.LOCATION_ID AND hps.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED' AND hp.STATUS = 'A' AND hp.PARTY_TYPE = 'PERSON' AND hcpref.CONTACT_LEVEL_TABLE(+) = 'HZ_PARTY_SITES' AND hcpref.CONTACT_LEVEL_TABLE_ID(+) = hps.PARTY_SITE_ID AND hcpref.PREFERENCE_END_DATE (+) is null AND hcpref.PREFERENCE_CODE (+) = 'DO'

View Text - HTML Formatted

SELECT HPS.PARTY_SITE_ID
, HPS.PARTY_ID
, HPS.LOCATION_ID
, HPS.PARTY_SITE_NAME
, HPS.PARTY_SITE_NUMBER
, HPS.IDENTIFYING_ADDRESS_FLAG
, HPS.REGION
, HPS.MAILSTOP
, HL.LANGUAGE LANGUAGE
, HPS.ADDRESSEE
, HPS.STATUS
, HCPREF.PREFERENCE_START_DATE
, HCPREF.PREFERENCE_START_TIME_HR
, HCPREF.PREFERENCE_START_TIME_MI
, HCPREF.PREFERENCE_END_DATE
, HCPREF.PREFERENCE_END_TIME_HR
, HCPREF.PREFERENCE_END_TIME_MI
FROM HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, HZ_CONTACT_PREFERENCES HCPREF
, HZ_LOCATIONS HL
WHERE HPS.STATUS = 'A'
AND HPS.PARTY_ID = HP.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HPS.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'PERSON'
AND HCPREF.CONTACT_LEVEL_TABLE(+) = 'HZ_PARTY_SITES'
AND HCPREF.CONTACT_LEVEL_TABLE_ID(+) = HPS.PARTY_SITE_ID
AND HCPREF.PREFERENCE_END_DATE (+) IS NULL
AND HCPREF.PREFERENCE_CODE (+) = 'DO'