View: CSC_CUST_ACCOUNTS_ROLES_V

Product:	CSC - Customer Care
Description:	This view retrieves the list of contacts for a given account
Implementation/DBA Data:	APPS.CSC_CUST_ACCOUNTS_ROLES_V

View Text

SELECT ROLES.ROWID
, ROLES.PARTY_ID
, ROLES.CUST_ACCOUNT_ID
, SITES.CUST_ACCT_SITE_ID
, ROLES.CUST_ACCOUNT_ROLE_ID
, SITES.PARTY_SITE_ID
, RTRIM(LTRIM(PARTY.PERSON_FIRST_NAME)) ||' '||LTRIM(RTRIM(PARTY.PERSON_LAST_NAME ))
, ROLES.ROLE_TYPE
, ROLE_TYPE.MEANING
, CURRENT_ROLE_STATE
, DECODE(CURRENT_ROLE_STATE
, 'A'
, 'ACTIVE'
, 'I'
, 'INACTIVE'
, 'D'
, 'DELETED')
, CURRENT_ROLE_STATE_EFFECTIVE
, ROLES.BEGIN_DATE
, ROLES.END_DATE
, PRIMARY_FLAG
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_MIDDLE_NAME
, LOCATIONS.ADDRESS1
, LOCATIONS.ADDRESS2
, LOCATIONS.ADDRESS3
, LOCATIONS.ADDRESS4
, LOCATIONS.CITY
, LOCATIONS.POSTAL_CODE
, LOCATIONS.STATE
, LOCATIONS.PROVINCE
, LOCATIONS.COUNTY
, LOCATIONS.COUNTRY
, TERRITORY.TERRITORY_SHORT_NAME
, ROLES.LAST_UPDATE_DATE
, ROLES.LAST_UPDATED_BY
, ROLES.CREATION_DATE
, ROLES.CREATED_BY
, ROLES.LAST_UPDATE_LOGIN
, ROLES.ATTRIBUTE_CATEGORY
, ROLES.ATTRIBUTE1
, ROLES.ATTRIBUTE2
, ROLES.ATTRIBUTE3
, ROLES.ATTRIBUTE4
, ROLES.ATTRIBUTE5
, ROLES.ATTRIBUTE6
, ROLES.ATTRIBUTE7
, ROLES.ATTRIBUTE8
, ROLES.ATTRIBUTE9
, ROLES.ATTRIBUTE10
, ROLES.ATTRIBUTE11
, ROLES.ATTRIBUTE12
, ROLES.ATTRIBUTE13
, ROLES.ATTRIBUTE14
, ROLES.ATTRIBUTE15
, ROLES.ATTRIBUTE16
, ROLES.ATTRIBUTE17
, ROLES.ATTRIBUTE18
, ROLES.ATTRIBUTE19
, ROLES.ATTRIBUTE20
, ROLES.ATTRIBUTE21
, ROLES.ATTRIBUTE22
, ROLES.ATTRIBUTE23
, ROLES.ATTRIBUTE24
FROM HZ_PARTIES PARTY
, HZ_RELATIONSHIPS RELATE
, HZ_CUST_ACCOUNT_ROLES ROLES
, HZ_CUST_ACCOUNTS ACCT
, HZ_CUST_ACCT_SITES SITES
, HZ_LOCATIONS LOCATIONS
, HZ_PARTY_SITES PARTY_SITES
, FND_TERRITORIES_TL TERRITORY
, AR_LOOKUPS ROLE_TYPE
WHERE RELATE.SUBJECT_ID = PARTY.PARTY_ID
AND ROLES.PARTY_ID = RELATE.PARTY_ID
AND ROLES.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID
AND ROLES.CUST_ACCOUNT_ID = SITES.CUST_ACCOUNT_ID (+)
AND ROLES.CUST_ACCT_SITE_ID = SITES.CUST_ACCT_SITE_ID (+)
AND PARTY_SITES.PARTY_SITE_ID (+) = SITES.PARTY_SITE_ID
AND LOCATIONS.LOCATION_ID (+) = PARTY_SITES.LOCATION_ID
AND LOCATIONS.COUNTRY = TERRITORY.TERRITORY_CODE (+)
AND ROLES.ROLE_TYPE = ROLE_TYPE.LOOKUP_CODE
AND PARTY.PARTY_TYPE = 'PERSON'
AND PARTY.PARTY_ID != ACCT.PARTY_ID
AND TRUNC(SYSDATE) BETWEEN ROLE_TYPE.START_DATE_ACTIVE
AND NVL(ROLE_TYPE.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND ROLE_TYPE.LOOKUP_TYPE = 'ACCT_ROLE_TYPE'

Columns