VIEW: APPS.AR_PHONES_V

Source

View Text - Preformatted

SELECT PHON.ROWID , PHON.CONTACT_POINT_ID , PHON.LAST_UPDATE_DATE , PHON.OBJECT_VERSION_NUMBER , PHON.LAST_UPDATED_BY , PHON.CREATION_DATE , PHON.CREATED_BY , DECODE(PHON.CONTACT_POINT_TYPE,'TLX',PHON.TELEX_NUMBER, PHON.PHONE_NUMBER) , PHON.STATUS , NVL(PHON.PHONE_LINE_TYPE,PHON.CONTACT_POINT_TYPE) , LOOK.MEANING , PHON.LAST_UPDATE_LOGIN , NULL /* Customer ID */ , NULL /* Address ID */ , NULL /* Contact Id */ , PHON.PHONE_COUNTRY_CODE , PHON.PHONE_AREA_CODE , PHON.PHONE_EXTENSION , PHON.PRIMARY_FLAG , PHON.ORIG_SYSTEM_REFERENCE , PHON.ATTRIBUTE_CATEGORY , PHON.ATTRIBUTE1 , PHON.ATTRIBUTE2 , PHON.ATTRIBUTE3 , PHON.ATTRIBUTE4 , PHON.ATTRIBUTE5 , PHON.ATTRIBUTE6 , PHON.ATTRIBUTE7 , PHON.ATTRIBUTE8 , PHON.ATTRIBUTE9 , PHON.ATTRIBUTE10 , PHON.ATTRIBUTE11 , PHON.ATTRIBUTE12 , PHON.ATTRIBUTE13 , PHON.ATTRIBUTE14 , PHON.ATTRIBUTE15 , PHON.CONTACT_POINT_TYPE , PHON.OWNER_TABLE_NAME , PHON.OWNER_TABLE_ID , PHON.TELEPHONE_TYPE , PHON.TIME_ZONE , PHON.PHONE_TOUCH_TONE_TYPE_FLAG , PHON.TELEX_NUMBER , PHON.WEB_TYPE , PHON.ATTRIBUTE16 , PHON.ATTRIBUTE17 , PHON.ATTRIBUTE18 , PHON.ATTRIBUTE19 , PHON.ATTRIBUTE20 , NULL /* Cust_contact_point_id */ ,phon.email_format ,look_format.meaning ,phon.email_address ,phon.url ,phon.contact_point_purpose ,look_purpose.meaning ,phon.primary_by_purpose FROM HZ_CONTACT_POINTS PHON, AR_LOOKUPS LOOK, AR_LOOKUPS LOOK_FORMAT, AR_LOOKUPS LOOK_PURPOSE WHERE PHON.CONTACT_POINT_TYPE NOT IN ( 'EDI') AND nvl(PHON.PHONE_LINE_TYPE, PHON.CONTACT_POINT_TYPE) = LOOK.LOOKUP_CODE AND ( (LOOK.LOOKUP_TYPE = 'COMMUNICATION_TYPE' and look.lookup_code in ('PHONE','TLX','EMAIL','WEB')) OR (LOOK.LOOKUP_TYPE = 'PHONE_LINE_TYPE') ) AND ( PHON.EMAIL_FORMAT = LOOK_FORMAT.LOOKUP_CODE(+) AND LOOK_FORMAT.LOOKUP_TYPE(+) = 'EMAIL_FORMAT') AND ( PHON.CONTACT_POINT_PURPOSE = LOOK_PURPOSE.LOOKUP_CODE AND LOOK_PURPOSE.LOOKUP_TYPE IN ('CONTACT_POINT_PURPOSE', 'CONTACT_POINT_PURPOSE_WEB' ) ) UNION SELECT PHON.ROWID , PHON.CONTACT_POINT_ID , PHON.LAST_UPDATE_DATE , PHON.OBJECT_VERSION_NUMBER , PHON.LAST_UPDATED_BY , PHON.CREATION_DATE , PHON.CREATED_BY , DECODE(PHON.CONTACT_POINT_TYPE,'TLX',PHON.TELEX_NUMBER, PHON.PHONE_NUMBER) , PHON.STATUS , NVL(PHON.PHONE_LINE_TYPE,PHON.CONTACT_POINT_TYPE) , LOOK.MEANING , PHON.LAST_UPDATE_LOGIN , NULL /* Customer ID */ , NULL /* Address ID */ , NULL /* Contact Id */ , PHON.PHONE_COUNTRY_CODE , PHON.PHONE_AREA_CODE , PHON.PHONE_EXTENSION , PHON.PRIMARY_FLAG , PHON.ORIG_SYSTEM_REFERENCE , PHON.ATTRIBUTE_CATEGORY , PHON.ATTRIBUTE1 , PHON.ATTRIBUTE2 , PHON.ATTRIBUTE3 , PHON.ATTRIBUTE4 , PHON.ATTRIBUTE5 , PHON.ATTRIBUTE6 , PHON.ATTRIBUTE7 , PHON.ATTRIBUTE8 , PHON.ATTRIBUTE9 , PHON.ATTRIBUTE10 , PHON.ATTRIBUTE11 , PHON.ATTRIBUTE12 , PHON.ATTRIBUTE13 , PHON.ATTRIBUTE14 , PHON.ATTRIBUTE15 , PHON.CONTACT_POINT_TYPE , PHON.OWNER_TABLE_NAME , PHON.OWNER_TABLE_ID , PHON.TELEPHONE_TYPE , PHON.TIME_ZONE , PHON.PHONE_TOUCH_TONE_TYPE_FLAG , PHON.TELEX_NUMBER , PHON.WEB_TYPE , PHON.ATTRIBUTE16 , PHON.ATTRIBUTE17 , PHON.ATTRIBUTE18 , PHON.ATTRIBUTE19 , PHON.ATTRIBUTE20 , NULL /* Cust_contact_point_id */ ,phon.email_format ,look_format.meaning ,phon.email_address ,phon.url ,phon.contact_point_purpose ,null ,null FROM HZ_CONTACT_POINTS PHON, AR_LOOKUPS LOOK, AR_LOOKUPS LOOK_FORMAT WHERE PHON.CONTACT_POINT_TYPE NOT IN ( 'EDI') AND nvl(PHON.PHONE_LINE_TYPE, PHON.CONTACT_POINT_TYPE) = LOOK.LOOKUP_CODE AND ( (LOOK.LOOKUP_TYPE = 'COMMUNICATION_TYPE' and look.lookup_code in ('PHONE','TLX','EMAIL','WEB')) OR (LOOK.LOOKUP_TYPE = 'PHONE_LINE_TYPE') ) AND ( PHON.EMAIL_FORMAT = LOOK_FORMAT.LOOKUP_CODE(+) AND LOOK_FORMAT.LOOKUP_TYPE(+) = 'EMAIL_FORMAT') AND PHON.CONTACT_POINT_PURPOSE IS NULL

View Text - HTML Formatted

SELECT PHON.ROWID
, PHON.CONTACT_POINT_ID
, PHON.LAST_UPDATE_DATE
, PHON.OBJECT_VERSION_NUMBER
, PHON.LAST_UPDATED_BY
, PHON.CREATION_DATE
, PHON.CREATED_BY
, DECODE(PHON.CONTACT_POINT_TYPE
, 'TLX'
, PHON.TELEX_NUMBER
, PHON.PHONE_NUMBER)
, PHON.STATUS
, NVL(PHON.PHONE_LINE_TYPE
, PHON.CONTACT_POINT_TYPE)
, LOOK.MEANING
, PHON.LAST_UPDATE_LOGIN
, NULL /* CUSTOMER ID */
, NULL /* ADDRESS ID */
, NULL /* CONTACT ID */
, PHON.PHONE_COUNTRY_CODE
, PHON.PHONE_AREA_CODE
, PHON.PHONE_EXTENSION
, PHON.PRIMARY_FLAG
, PHON.ORIG_SYSTEM_REFERENCE
, PHON.ATTRIBUTE_CATEGORY
, PHON.ATTRIBUTE1
, PHON.ATTRIBUTE2
, PHON.ATTRIBUTE3
, PHON.ATTRIBUTE4
, PHON.ATTRIBUTE5
, PHON.ATTRIBUTE6
, PHON.ATTRIBUTE7
, PHON.ATTRIBUTE8
, PHON.ATTRIBUTE9
, PHON.ATTRIBUTE10
, PHON.ATTRIBUTE11
, PHON.ATTRIBUTE12
, PHON.ATTRIBUTE13
, PHON.ATTRIBUTE14
, PHON.ATTRIBUTE15
, PHON.CONTACT_POINT_TYPE
, PHON.OWNER_TABLE_NAME
, PHON.OWNER_TABLE_ID
, PHON.TELEPHONE_TYPE
, PHON.TIME_ZONE
, PHON.PHONE_TOUCH_TONE_TYPE_FLAG
, PHON.TELEX_NUMBER
, PHON.WEB_TYPE
, PHON.ATTRIBUTE16
, PHON.ATTRIBUTE17
, PHON.ATTRIBUTE18
, PHON.ATTRIBUTE19
, PHON.ATTRIBUTE20
, NULL /* CUST_CONTACT_POINT_ID */
, PHON.EMAIL_FORMAT
, LOOK_FORMAT.MEANING
, PHON.EMAIL_ADDRESS
, PHON.URL
, PHON.CONTACT_POINT_PURPOSE
, LOOK_PURPOSE.MEANING
, PHON.PRIMARY_BY_PURPOSE
FROM HZ_CONTACT_POINTS PHON
, AR_LOOKUPS LOOK
, AR_LOOKUPS LOOK_FORMAT
, AR_LOOKUPS LOOK_PURPOSE
WHERE PHON.CONTACT_POINT_TYPE NOT IN ( 'EDI')
AND NVL(PHON.PHONE_LINE_TYPE
, PHON.CONTACT_POINT_TYPE) = LOOK.LOOKUP_CODE
AND ( (LOOK.LOOKUP_TYPE = 'COMMUNICATION_TYPE'
AND LOOK.LOOKUP_CODE IN ('PHONE'
, 'TLX'
, 'EMAIL'
, 'WEB')) OR (LOOK.LOOKUP_TYPE = 'PHONE_LINE_TYPE') )
AND ( PHON.EMAIL_FORMAT = LOOK_FORMAT.LOOKUP_CODE(+)
AND LOOK_FORMAT.LOOKUP_TYPE(+) = 'EMAIL_FORMAT')
AND ( PHON.CONTACT_POINT_PURPOSE = LOOK_PURPOSE.LOOKUP_CODE
AND LOOK_PURPOSE.LOOKUP_TYPE IN ('CONTACT_POINT_PURPOSE'
, 'CONTACT_POINT_PURPOSE_WEB' ) ) UNION SELECT PHON.ROWID
, PHON.CONTACT_POINT_ID
, PHON.LAST_UPDATE_DATE
, PHON.OBJECT_VERSION_NUMBER
, PHON.LAST_UPDATED_BY
, PHON.CREATION_DATE
, PHON.CREATED_BY
, DECODE(PHON.CONTACT_POINT_TYPE
, 'TLX'
, PHON.TELEX_NUMBER
, PHON.PHONE_NUMBER)
, PHON.STATUS
, NVL(PHON.PHONE_LINE_TYPE
, PHON.CONTACT_POINT_TYPE)
, LOOK.MEANING
, PHON.LAST_UPDATE_LOGIN
, NULL /* CUSTOMER ID */
, NULL /* ADDRESS ID */
, NULL /* CONTACT ID */
, PHON.PHONE_COUNTRY_CODE
, PHON.PHONE_AREA_CODE
, PHON.PHONE_EXTENSION
, PHON.PRIMARY_FLAG
, PHON.ORIG_SYSTEM_REFERENCE
, PHON.ATTRIBUTE_CATEGORY
, PHON.ATTRIBUTE1
, PHON.ATTRIBUTE2
, PHON.ATTRIBUTE3
, PHON.ATTRIBUTE4
, PHON.ATTRIBUTE5
, PHON.ATTRIBUTE6
, PHON.ATTRIBUTE7
, PHON.ATTRIBUTE8
, PHON.ATTRIBUTE9
, PHON.ATTRIBUTE10
, PHON.ATTRIBUTE11
, PHON.ATTRIBUTE12
, PHON.ATTRIBUTE13
, PHON.ATTRIBUTE14
, PHON.ATTRIBUTE15
, PHON.CONTACT_POINT_TYPE
, PHON.OWNER_TABLE_NAME
, PHON.OWNER_TABLE_ID
, PHON.TELEPHONE_TYPE
, PHON.TIME_ZONE
, PHON.PHONE_TOUCH_TONE_TYPE_FLAG
, PHON.TELEX_NUMBER
, PHON.WEB_TYPE
, PHON.ATTRIBUTE16
, PHON.ATTRIBUTE17
, PHON.ATTRIBUTE18
, PHON.ATTRIBUTE19
, PHON.ATTRIBUTE20
, NULL /* CUST_CONTACT_POINT_ID */
, PHON.EMAIL_FORMAT
, LOOK_FORMAT.MEANING
, PHON.EMAIL_ADDRESS
, PHON.URL
, PHON.CONTACT_POINT_PURPOSE
, NULL
, NULL
FROM HZ_CONTACT_POINTS PHON
, AR_LOOKUPS LOOK
, AR_LOOKUPS LOOK_FORMAT
WHERE PHON.CONTACT_POINT_TYPE NOT IN ( 'EDI')
AND NVL(PHON.PHONE_LINE_TYPE
, PHON.CONTACT_POINT_TYPE) = LOOK.LOOKUP_CODE
AND ( (LOOK.LOOKUP_TYPE = 'COMMUNICATION_TYPE'
AND LOOK.LOOKUP_CODE IN ('PHONE'
, 'TLX'
, 'EMAIL'
, 'WEB')) OR (LOOK.LOOKUP_TYPE = 'PHONE_LINE_TYPE') )
AND ( PHON.EMAIL_FORMAT = LOOK_FORMAT.LOOKUP_CODE(+)
AND LOOK_FORMAT.LOOKUP_TYPE(+) = 'EMAIL_FORMAT')
AND PHON.CONTACT_POINT_PURPOSE IS NULL