View: AMS_P_ACCT_GUARANTOR_V

View Text

SELECT R.CUST_ACCOUNT_ROLE_ID AMS_ACCT_GUARANTOR_ID
, R.PARTY_ID
, R.CUST_ACCOUNT_ID ACCT_ID
, R.CURRENT_ROLE_STATE
, R.CURRENT_ROLE_STATE_EFFECTIVE
, R.CUST_ACCT_SITE_ID
, S.PARTY_SITE_ID
, R.BEGIN_DATE
, R.END_DATE
, R.PRIMARY_FLAG
, R.ROLE_TYPE
, R.LAST_UPDATE_DATE
, R.SOURCE_CODE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, P.PARTY_NUMBER
, P.PARTY_NAME
, P.PARTY_TYPE
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, L.LOCATION_ID
, L.ATTRIBUTE_CATEGORY
, L.ATTRIBUTE1
, L.ATTRIBUTE2
, L.ATTRIBUTE3
, L.ATTRIBUTE4
, L.ATTRIBUTE5
, L.ATTRIBUTE6
, L.ATTRIBUTE7
, L.ATTRIBUTE8
, L.ATTRIBUTE9
, L.ATTRIBUTE10
, L.ATTRIBUTE11
, L.ATTRIBUTE12
, L.ATTRIBUTE13
, L.ATTRIBUTE14
, L.ATTRIBUTE15
, L.ATTRIBUTE16
, L.ATTRIBUTE17
, L.ATTRIBUTE18
, L.ATTRIBUTE19
, L.ATTRIBUTE20
, L.GLOBAL_ATTRIBUTE_CATEGORY
, L.GLOBAL_ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE2
, L.GLOBAL_ATTRIBUTE3
, L.GLOBAL_ATTRIBUTE4
, L.GLOBAL_ATTRIBUTE5
, L.GLOBAL_ATTRIBUTE6
, L.GLOBAL_ATTRIBUTE7
, L.GLOBAL_ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE9
, L.GLOBAL_ATTRIBUTE10
, L.GLOBAL_ATTRIBUTE11
, L.GLOBAL_ATTRIBUTE12
, L.GLOBAL_ATTRIBUTE13
, L.GLOBAL_ATTRIBUTE14
, L.GLOBAL_ATTRIBUTE15
, L.GLOBAL_ATTRIBUTE16
, L.GLOBAL_ATTRIBUTE17
, L.GLOBAL_ATTRIBUTE18
, L.GLOBAL_ATTRIBUTE19
, L.GLOBAL_ATTRIBUTE20
, L.ORIG_SYSTEM_REFERENCE
, L.COUNTRY
, L.ADDRESS1
, L.ADDRESS2
, L.ADDRESS3
, L.ADDRESS4
, L.CITY
, L.POSTAL_CODE
, L.STATE
, L.PROVINCE
, L.COUNTY
, L.ADDRESS_KEY
, L.ADDRESS_STYLE
, L.VALIDATED_FLAG
, L.ADDRESS_LINES_PHONETIC
, L.APARTMENT_FLAG
, L.PO_BOX_NUMBER
, L.HOUSE_NUMBER
, L.STREET_SUFFIX
, L.APARTMENT_NUMBER
, L.SECONDARY_SUFFIX_ELEMENT
, L.STREET
, L.RURAL_ROUTE_TYPE
, L.RURAL_ROUTE_NUMBER
, L.STREET_NUMBER
, L.BUILDING
, L.FLOOR
, L.SUITE
, L.ROOM
, L.POSTAL_PLUS4_CODE
, L.TIME_ZONE
, L.OVERSEAS_ADDRESS_FLAG
, L.POST_OFFICE
, L.POSITION
, L.DELIVERY_POINT_CODE
, L.LOCATION_DIRECTIONS
, L.ADDRESS_EFFECTIVE_DATE
, L.ADDRESS_EXPIRATION_DATE
, L.ADDRESS_ERROR_CODE
, L.CLLI_CODE
, L.DODAAC
, L.TRAILING_DIRECTORY_CODE
, L.LANGUAGE
, L.LIFE_CYCLE_STATUS
, L.SHORT_DESCRIPTION
, L.DESCRIPTION
, L.CONTENT_SOURCE_TYPE
, L.LOC_HIERARCHY_ID
, L.SALES_TAX_GEOCODE
, L.SALES_TAX_INSIDE_CITY_LIMITS
FROM HZ_CUST_ACCOUNT_ROLES R
, HZ_CUST_ACCOUNTS A
, HZ_CUST_ACCT_SITES_ALL CS
, HZ_PARTY_SITES S
, HZ_PARTIES P
, HZ_LOCATIONS L
WHERE A.CUST_ACCOUNT_ID = R.CUST_ACCOUNT_ID
AND R.CUST_ACCOUNT_ROLE_ID = (SELECT MAX(R1.CUST_ACCOUNT_ROLE_ID)
FROM HZ_CUST_ACCOUNT_ROLES R1
, HZ_PARTIES P1 WHERE R1.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID AND R1.ROLE_TYPE = 'GUARANTOR' AND R.CURRENT_ROLE_STATE = 'A' AND P1.PARTY_ID = R1.PARTY_ID AND P1.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION') )
AND R.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND CS.PARTY_SITE_ID = S.PARTY_SITE_ID
AND R.PARTY_ID = P.PARTY_ID
AND S.LOCATION_ID = L.LOCATION_ID UNION SELECT R.CUST_ACCOUNT_ROLE_ID AMS_ACCT_GUARANTOR_ID
, R.PARTY_ID
, R.CUST_ACCOUNT_ID
, R.CURRENT_ROLE_STATE
, R.CURRENT_ROLE_STATE_EFFECTIVE
, R.CUST_ACCT_SITE_ID
, S.PARTY_SITE_ID
, R.BEGIN_DATE
, R.END_DATE
, R.PRIMARY_FLAG
, R.ROLE_TYPE
, R.LAST_UPDATE_DATE
, R.SOURCE_CODE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, P.PARTY_NUMBER
, P.PARTY_NAME
, P.PARTY_TYPE
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, L.LOCATION_ID
, L.ATTRIBUTE_CATEGORY
, L.ATTRIBUTE1
, L.ATTRIBUTE2
, L.ATTRIBUTE3
, L.ATTRIBUTE4
, L.ATTRIBUTE5
, L.ATTRIBUTE6
, L.ATTRIBUTE7
, L.ATTRIBUTE8
, L.ATTRIBUTE9
, L.ATTRIBUTE10
, L.ATTRIBUTE11
, L.ATTRIBUTE12
, L.ATTRIBUTE13
, L.ATTRIBUTE14
, L.ATTRIBUTE15
, L.ATTRIBUTE16
, L.ATTRIBUTE17
, L.ATTRIBUTE18
, L.ATTRIBUTE19
, L.ATTRIBUTE20
, L.GLOBAL_ATTRIBUTE_CATEGORY
, L.GLOBAL_ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE2
, L.GLOBAL_ATTRIBUTE3
, L.GLOBAL_ATTRIBUTE4
, L.GLOBAL_ATTRIBUTE5
, L.GLOBAL_ATTRIBUTE6
, L.GLOBAL_ATTRIBUTE7
, L.GLOBAL_ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE9
, L.GLOBAL_ATTRIBUTE10
, L.GLOBAL_ATTRIBUTE11
, L.GLOBAL_ATTRIBUTE12
, L.GLOBAL_ATTRIBUTE13
, L.GLOBAL_ATTRIBUTE14
, L.GLOBAL_ATTRIBUTE15
, L.GLOBAL_ATTRIBUTE16
, L.GLOBAL_ATTRIBUTE17
, L.GLOBAL_ATTRIBUTE18
, L.GLOBAL_ATTRIBUTE19
, L.GLOBAL_ATTRIBUTE20
, L.ORIG_SYSTEM_REFERENCE
, L.COUNTRY
, L.ADDRESS1
, L.ADDRESS2
, L.ADDRESS3
, L.ADDRESS4
, L.CITY
, L.POSTAL_CODE
, L.STATE
, L.PROVINCE
, L.COUNTY
, L.ADDRESS_KEY
, L.ADDRESS_STYLE
, L.VALIDATED_FLAG
, L.ADDRESS_LINES_PHONETIC
, L.APARTMENT_FLAG
, L.PO_BOX_NUMBER
, L.HOUSE_NUMBER
, L.STREET_SUFFIX
, L.APARTMENT_NUMBER
, L.SECONDARY_SUFFIX_ELEMENT
, L.STREET
, L.RURAL_ROUTE_TYPE
, L.RURAL_ROUTE_NUMBER
, L.STREET_NUMBER
, L.BUILDING
, L.FLOOR
, L.SUITE
, L.ROOM
, L.POSTAL_PLUS4_CODE
, L.TIME_ZONE
, L.OVERSEAS_ADDRESS_FLAG
, L.POST_OFFICE
, L.POSITION
, L.DELIVERY_POINT_CODE
, L.LOCATION_DIRECTIONS
, L.ADDRESS_EFFECTIVE_DATE
, L.ADDRESS_EXPIRATION_DATE
, L.ADDRESS_ERROR_CODE
, L.CLLI_CODE
, L.DODAAC
, L.TRAILING_DIRECTORY_CODE
, L.LANGUAGE
, L.LIFE_CYCLE_STATUS
, L.SHORT_DESCRIPTION
, L.DESCRIPTION
, L.CONTENT_SOURCE_TYPE
, L.LOC_HIERARCHY_ID
, L.SALES_TAX_GEOCODE
, L.SALES_TAX_INSIDE_CITY_LIMITS
FROM HZ_CUST_ACCOUNT_ROLES R
, HZ_CUST_ACCOUNTS A
, HZ_CUST_ACCT_SITES_ALL CS
, HZ_PARTY_SITES S
, HZ_PARTIES P
, HZ_PARTIES P2
, HZ_PARTY_RELATIONSHIPS REL
, HZ_LOCATIONS L
WHERE A.CUST_ACCOUNT_ID = R.CUST_ACCOUNT_ID
AND R.CUST_ACCOUNT_ROLE_ID = (SELECT MAX(R1.CUST_ACCOUNT_ROLE_ID)
FROM HZ_CUST_ACCOUNT_ROLES R1
, HZ_PARTIES P1 WHERE R1.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID AND R1.ROLE_TYPE = 'GUARANTOR' AND R.CURRENT_ROLE_STATE = 'A' AND P1.PARTY_ID = R1.PARTY_ID AND P1.PARTY_TYPE = ('PARTY_RELATIONSHIP') )
AND R.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND CS.PARTY_SITE_ID = S.PARTY_SITE_ID
AND S.LOCATION_ID = L.LOCATION_ID
AND R.PARTY_ID = P2.PARTY_ID
AND REL.PARTY_ID = P2.PARTY_ID
AND REL.OBJECT_ID = P.PARTY_ID

Columns