Product: | AMS - Marketing |
---|---|
Description: | This view returns the primary bill to address for an account. Used for List generation only. |
Implementation/DBA Data: | Not implemented in this database |
SELECT ACCT_SITE.PARTY_SITE_ID AMS_ACCT_BILL_TO_ID
, ACCT.CUST_ACCOUNT_ID ACCT_ID
, ACCT_SITE.CUST_ACCT_SITE_ID
, P.PARTY_ID
, 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.LAST_UPDATE_DATE
, L.LAST_UPDATED_BY
, L.CREATION_DATE
, L.CREATED_BY
, L.LAST_UPDATE_LOGIN
, L.REQUEST_ID
, L.PROGRAM_APPLICATION_ID
, L.PROGRAM_ID
, L.PROGRAM_UPDATE_DATE
, L.WH_UPDATE_DATE
, 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_ACCOUNTS ACCT
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS L
, HZ_PARTIES P
WHERE ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.PARTY_SITE_ID = (SELECT MAX(ACCT_SITE1.PARTY_SITE_ID)
FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE1
, HZ_CUST_SITE_USES_ALL SITE_USE1
WHERE ACCT_SITE1.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID
AND SITE_USE1.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE_USE1.SITE_USE_CODE = 'BILL_TO' )
AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
AND SITE.LOCATION_ID = L.LOCATION_ID
AND SITE.PARTY_ID = P.PARTY_ID