VIEW: APPS.AST_CUST_ACCOUNTS_ROLES_V

Source

View Text - Preformatted

SELECT roles.rowid ,roles.party_id ,roles.cust_account_id ,sites.cust_acct_site_id ,roles.cust_account_role_id ,sites.party_site_id ,party.party_name ,roles.role_type ,role_type.meaning ,current_role_state ,arlkup.meaning ,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 ,roles.object_version_number ,roles.created_by_module ,roles.application_id 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, AR_LOOKUPS arlkup 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 territory.language(+) = userenv('LANG') AND roles.role_type = role_type.lookup_code 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' and arlkup.lookup_type(+) = 'REGISTRY_STATUS' and arlkup.lookup_code(+) = roles.current_role_state

View Text - HTML Formatted

SELECT ROLES.ROWID
, ROLES.PARTY_ID
, ROLES.CUST_ACCOUNT_ID
, SITES.CUST_ACCT_SITE_ID
, ROLES.CUST_ACCOUNT_ROLE_ID
, SITES.PARTY_SITE_ID
, PARTY.PARTY_NAME
, ROLES.ROLE_TYPE
, ROLE_TYPE.MEANING
, CURRENT_ROLE_STATE
, ARLKUP.MEANING
, 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
, ROLES.OBJECT_VERSION_NUMBER
, ROLES.CREATED_BY_MODULE
, ROLES.APPLICATION_ID
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
, AR_LOOKUPS ARLKUP
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 TERRITORY.LANGUAGE(+) = USERENV('LANG')
AND ROLES.ROLE_TYPE = ROLE_TYPE.LOOKUP_CODE
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'
AND ARLKUP.LOOKUP_TYPE(+) = 'REGISTRY_STATUS'
AND ARLKUP.LOOKUP_CODE(+) = ROLES.CURRENT_ROLE_STATE