FND Design Data [Home] [Help]

View: AMS_PARTIES_V

Product: AMS - Marketing
Description:
Implementation/DBA Data: ViewAPPS.AMS_PARTIES_V
View Text

SELECT PRSN.PARTY_ID
, PRSN.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PRSN.STATUS
, 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
, PRSN.EMAIL_ADDRESS
, PRSN.ADDRESS1 || DECODE(PRSN.ADDRESS2
, NULL
, ''
, ' '||PRSN.ADDRESS2) || DECODE(PRSN.ADDRESS3
, NULL
, ''
, ' '||PRSN.ADDRESS3) || DECODE(PRSN.ADDRESS4
, NULL
, ''
, ' '||PRSN.ADDRESS4) || '
, ' ||PRSN.CITY ||'
, '||PRSN.STATE||'
, '||PRSN.POSTAL_CODE||'
, '||PRSN.COUNTRY
, PRSN.ADDRESS1 || DECODE(PRSN.ADDRESS2
, NULL
, ''
, ' '||PRSN.ADDRESS2) || DECODE(PRSN.ADDRESS3
, NULL
, ''
, ' '|| PRSN.ADDRESS3) || DECODE(PRSN.ADDRESS4
, NULL
, ''
, ' '||PRSN.ADDRESS4)
, PRSN.ADDRESS1
, PRSN.ADDRESS2
, PRSN.ADDRESS3
, PRSN.ADDRESS4
, PRSN.STATE
, PRSN.CITY
, PRSN.PROVINCE
, PRSN.COUNTY
, PRSN.POSTAL_CODE
, PRSN.COUNTRY
, PRSN.LAST_ORDERED_DATE
, 0
, ''
, ''
, 0
, ''
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
FROM HZ_PARTIES PRSN
, HZ_CONTACT_POINTS CPPH
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PRSN.STATUS = 'A'
AND PRSN.PARTY_ID = CPPH.OWNER_TABLE_ID (+)
AND CPPH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CPPH.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CPPH.PRIMARY_FLAG (+) = 'Y' UNION SELECT PRSN.PARTY_ID
, PR.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PR.STATUS
, 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.LAST_ORDERED_DATE
, PR.OBJECT_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PR.RELATIONSHIP_ID
, PR.RELATIONSHIP_TYPE
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
FROM HZ_PARTIES PRSN
, HZ_PARTIES PARTY
, HZ_PARTIES RELPARTY
, HZ_CONTACT_POINTS CPPH
, HZ_ORG_CONTACTS C
, HZ_RELATIONSHIPS PR
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PR.STATUS='A'
AND PRSN.STATUS='A'
AND PARTY.STATUS='A'
AND PRSN.PARTY_ID = PR.SUBJECT_ID
AND PR.OBJECT_ID = PARTY.PARTY_ID
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.OBJECT_TYPE='ORGANIZATION'
AND PR.RELATIONSHIP_CODE IN('CONTACT'
, 'CONTACT_OF'
, 'EMPLOYEE_OF')
AND PR.DIRECTIONAL_FLAG='F'
AND C.PARTY_RELATIONSHIP_ID=PR.RELATIONSHIP_ID

Columns

Name
PERSON_ID
PARTY_ID
PERSON_NAME
LAST_NAME
FIRST_NAME
MIDDLE_NAME
PREFERRED_NAME
STATUS_CODE
CONCATENATED_PHONE
PHONE_COUNTRY_CODE
PHONE_AREA_CODE
PHONE_NUMBER
PHONE_EXTENSION
PHONE_TYPE
EMAIL_ADDRESS
CONCATENATED_ADDRESS
ADDRESS
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
STATE
CITY
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
LAST_ORDER_DATE
RELATED_PARTY_ID
RELATED_PARTY_TYPE
RELATED_PARTY_NAME
RELATIONSHIP_ID
RELATIONSHIP_TYPE
CREATION_DATE
LAST_UPDATE_DATE
CREATED_BY
LAST_UPDATED_BY