Product: | CSC - Customer Care |
---|---|
Description: | Contact Information for a customer |
Implementation/DBA Data: | APPS.CSC_HZ_PARTIES_CONT_INFO_V |
SELECT SUB.ROWID
, SUB.PARTY_ID
, SUB.PARTY_NUMBER
, SUB.PARTY_NAME
, SUB.PARTY_TYPE
, SUB.LAST_UPDATE_DATE
, SUB.ATTRIBUTE_CATEGORY
, SUB.ATTRIBUTE1
, SUB.ATTRIBUTE2
, SUB.ATTRIBUTE3
, SUB.ATTRIBUTE4
, SUB.ATTRIBUTE5
, SUB.ATTRIBUTE6
, SUB.ATTRIBUTE7
, SUB.ATTRIBUTE8
, SUB.ATTRIBUTE9
, SUB.ATTRIBUTE10
, SUB.ATTRIBUTE11
, SUB.ATTRIBUTE12
, SUB.ATTRIBUTE13
, SUB.ATTRIBUTE14
, SUB.ATTRIBUTE15
, SUB.ATTRIBUTE16
, SUB.ATTRIBUTE17
, SUB.ATTRIBUTE18
, SUB.ATTRIBUTE19
, SUB.ATTRIBUTE20
, SUB.ATTRIBUTE21
, SUB.ATTRIBUTE22
, SUB.ATTRIBUTE23
, SUB.ATTRIBUTE24
, SUB.PERSON_PRE_NAME_ADJUNCT
, SUB.PERSON_FIRST_NAME
, SUB.PERSON_MIDDLE_NAME
, SUB.PERSON_LAST_NAME
, SUB.PERSON_NAME_SUFFIX
, SUB.PERSON_TITLE
, SUB.ADDRESS1||' '||SUB.ADDRESS2||' '||SUB.ADDRESS3||' '||SUB.ADDRESS4 SUB_ADDRESS
, SUB.ADDRESS1
, SUB.ADDRESS2
, SUB.ADDRESS3
, SUB.ADDRESS4
, SUB.CITY
, SUB.STATE
, SUB.PROVINCE
, SUB.POSTAL_CODE
, SUB.COUNTRY
, SUB.STATUS
, SUB.COUNTY
, SUB.URL
, SUB.EMAIL_ADDRESS
, SUB.ORGANIZATION_NAME_PHONETIC
, SUB.PERSON_FIRST_NAME_PHONETIC
, SUB.PERSON_LAST_NAME_PHONETIC
, REL.PARTY_ID
, REL.PARTY_NUMBER
, REL.PARTY_NAME
, REL.PARTY_TYPE
, REL.LAST_UPDATE_DATE
, REL.ATTRIBUTE_CATEGORY
, REL.ATTRIBUTE1
, REL.ATTRIBUTE2
, REL.ATTRIBUTE3
, REL.ATTRIBUTE4
, REL.ATTRIBUTE5
, REL.ATTRIBUTE6
, REL.ATTRIBUTE7
, REL.ATTRIBUTE8
, REL.ATTRIBUTE9
, REL.ATTRIBUTE10
, REL.ATTRIBUTE11
, REL.ATTRIBUTE12
, REL.ATTRIBUTE13
, REL.ATTRIBUTE14
, REL.ATTRIBUTE15
, REL.ATTRIBUTE16
, REL.ATTRIBUTE17
, REL.ATTRIBUTE18
, REL.ATTRIBUTE19
, REL.ATTRIBUTE20
, REL.ATTRIBUTE21
, REL.ATTRIBUTE22
, REL.ATTRIBUTE23
, REL.ATTRIBUTE24
, REL.PERSON_PRE_NAME_ADJUNCT
, REL.PERSON_FIRST_NAME
, REL.PERSON_MIDDLE_NAME
, REL.PERSON_LAST_NAME
, REL.PERSON_NAME_SUFFIX
, REL.PERSON_TITLE
, REL.ADDRESS1||' '||REL.ADDRESS2||' '||REL.ADDRESS3||' '||REL.ADDRESS4 REL_ADDRESS
, REL.ADDRESS1
, REL.ADDRESS2
, REL.ADDRESS3
, REL.ADDRESS4
, REL.CITY
, REL.STATE
, REL.PROVINCE
, REL.POSTAL_CODE
, REL.COUNTRY
, REL.STATUS
, REL.COUNTY
, REL.URL
, REL.EMAIL_ADDRESS
, REL.ORGANIZATION_NAME_PHONETIC
, REL.PERSON_FIRST_NAME_PHONETIC
, REL.PERSON_LAST_NAME_PHONETIC
, PARTY_REL.RELATIONSHIP_ID
, PARTY_REL.PARTY_ID
, PARTY_REL.SUBJECT_ID
, PARTY_REL.SUBJECT_TYPE
, PARTY_REL.SUBJECT_TABLE_NAME
, PARTY_REL.OBJECT_ID
, PARTY_REL.OBJECT_TYPE
, PARTY_REL.OBJECT_TABLE_NAME
, PARTY_REL.RELATIONSHIP_CODE
, PARTY_REL.DIRECTIONAL_FLAG
, PARTY_REL.START_DATE
, PARTY_REL.END_DATE
, PARTY_REL.STATUS
, PARTY_REL.RELATIONSHIP_TYPE
, PARTY_REL.LAST_UPDATE_DATE
, LKUP.MEANING
, TERI.TERRITORY_SHORT_NAME
FROM HZ_PARTIES SUB
, HZ_PARTIES REL
, HZ_RELATIONSHIPS PARTY_REL
, AR_LOOKUPS LKUP
, FND_TERRITORIES_VL TERI
WHERE SUB.PARTY_TYPE = 'PERSON'
AND SUB.PARTY_ID = PARTY_REL.SUBJECT_ID
AND REL.PARTY_ID = PARTY_REL.PARTY_ID
AND REL.COUNTRY = TERI.TERRITORY_CODE(+)
AND LKUP.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND PARTY_REL.RELATIONSHIP_CODE = LKUP.LOOKUP_CODE
AND LKUP.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LKUP.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(LKUP.END_DATE_ACTIVE
, SYSDATE))
AND NVL(PARTY_REL.START_DATE
, SYSDATE-1) < SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE+1) > SYSDATE
AND REL.STATUS NOT IN ('M'
, 'D')
AND SUB.STATUS NOT IN ('M'
, 'D') UNION ALL SELECT PRTY.ROWID
, PARTY_ID
, NULL
, PARTY_NAME
, NULL
, PRTY.LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, NULL
, PERSON_TITLE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, STATUS
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PARTY_ID
, PARTY_NUMBER
, PARTY_NAME
, PARTY_TYPE
, PRTY.LAST_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE21
, ATTRIBUTE22
, ATTRIBUTE23
, ATTRIBUTE24
, PERSON_PRE_NAME_ADJUNCT
, PERSON_FIRST_NAME
, PERSON_MIDDLE_NAME
, PERSON_LAST_NAME
, PERSON_NAME_SUFFIX
, PERSON_TITLE
, ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4 ADDRESS
, ADDRESS1
, ADDRESS2
, ADDRESS3
, ADDRESS4
, CITY
, STATE
, PROVINCE
, POSTAL_CODE
, COUNTRY
, STATUS
, COUNTY
, URL
, EMAIL_ADDRESS
, ORGANIZATION_NAME_PHONETIC
, PERSON_FIRST_NAME_PHONETIC
, PERSON_LAST_NAME_PHONETIC
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PARTY_ID
, NULL
, NULL
, PARTY_ID
, NULL
, NULL
, 'SELF'
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, TO_DATE(NULL)
, NULL
, TERI.TERRITORY_SHORT_NAME
FROM HZ_PARTIES PRTY
, FND_TERRITORIES_VL TERI
WHERE PARTY_TYPE ='PERSON'
AND PRTY.COUNTRY = TERI.TERRITORY_CODE(+)
AND STATUS NOT IN ('M'
, 'D')