[Home] [Help]
| Product: | ASF - Sales Online |
|---|---|
| Description: | View of contact person information |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PRSN.PARTY_ID
, PR.PARTY_ID
, PARTY.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PR.STATUS
, PRSN.LAST_ORDERED_DATE
, PR.OBJECT_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PR.RELATIONSHIP_ID
, PR.RELATIONSHIP_CODE
, AL1.MEANING
, CPPH.PHONE_COUNTRY_CODE || DECODE(CPPH.PHONE_AREA_CODE
, NULL
, ''
, ' '||CPPH.PHONE_AREA_CODE) || DECODE(CPPH.PHONE_NUMBER
, NULL
, ''
, ' '||CPPH.PHONE_NUMBER) || DECODE(CPPH.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||CPPH.PHONE_EXTENSION)
, CPPH.PHONE_COUNTRY_CODE
, CPPH.PHONE_AREA_CODE
, CPPH.PHONE_NUMBER
, CPPH.PHONE_EXTENSION
, CPPH.PHONE_LINE_TYPE
, RELPARTY.EMAIL_ADDRESS
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4) || '
, '||RELPARTY.CITY ||'
, '||RELPARTY.STATE||'
, '||RELPARTY.POSTAL_CODE||'
, '||RELPARTY.COUNTRY
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4)
, RELPARTY.ADDRESS1
, RELPARTY.ADDRESS2
, RELPARTY.ADDRESS3
, RELPARTY.ADDRESS4
, RELPARTY.STATE
, RELPARTY.CITY
, RELPARTY.PROVINCE
, RELPARTY.COUNTY
, RELPARTY.POSTAL_CODE
, RELPARTY.COUNTRY
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
, PRSN.ATTRIBUTE1
, PRSN.ATTRIBUTE2
, PRSN.ATTRIBUTE3
, PRSN.ATTRIBUTE4
, PRSN.ATTRIBUTE5
, PRSN.ATTRIBUTE6
, PRSN.ATTRIBUTE7
, PRSN.ATTRIBUTE8
, PRSN.ATTRIBUTE9
, PRSN.ATTRIBUTE10
, PRSN.ATTRIBUTE11
, PRSN.ATTRIBUTE12
, PRSN.ATTRIBUTE13
, PRSN.ATTRIBUTE14
, PRSN.ATTRIBUTE15
, PRSN.ATTRIBUTE16
, PRSN.ATTRIBUTE17
, PRSN.ATTRIBUTE18
, PRSN.ATTRIBUTE19
, PRSN.ATTRIBUTE20
, PRSN.ATTRIBUTE21
, PRSN.ATTRIBUTE22
, PRSN.ATTRIBUTE23
, PRSN.ATTRIBUTE24
, PRSN.PERSON_FIRST_NAME_PHONETIC
, PRSN.PERSON_LAST_NAME_PHONETIC
, PRSN.LANGUAGE_NAME
, PRSN.PERSON_PRE_NAME_ADJUNCT
, PRSN.PERSON_ACADEMIC_TITLE
, PRSN.PERSON_NAME_SUFFIX
, PRSN.TOTAL_ORDERED_AMOUNT
, PRSN.ORIG_SYSTEM_REFERENCE
FROM HZ_PARTIES PRSN
, HZ_RELATIONSHIPS PR
, AR_LOOKUPS AL1
, HZ_PARTIES PARTY
, HZ_PARTIES RELPARTY
, HZ_CONTACT_POINTS CPPH
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PRSN.PARTY_ID = PR.SUBJECT_ID
AND PR.OBJECT_ID = PARTY.PARTY_ID
AND PR.RELATIONSHIP_CODE = AL1.LOOKUP_CODE
AND AL1.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND AL1.ENABLED_FLAG = 'Y'
AND PR.PARTY_ID = RELPARTY.PARTY_ID
AND PR.PARTY_ID = CPPH.OWNER_TABLE_ID (+)
AND CPPH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CPPH.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CPPH.PRIMARY_FLAG (+) = 'Y'
AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PRSN.STATUS IN ('A'
, 'I')
AND PR.STATUS IN ('A'
, 'I')
AND PARTY.STATUS IN ('A'
, 'I')
AND RELPARTY.STATUS IN ('A'
, 'I')
AND DECODE(CPPH.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1