FND Design Data [Home] [Help]

View: AMS_ORG_ACCT_V

Product: AMS - Marketing
Description: Customer account information
Implementation/DBA Data: ViewAPPS.AMS_ORG_ACCT_V
View Text

SELECT HCA.CUST_ACCOUNT_ID
, HCA.PARTY_ID
, HP.PARTY_NAME
, HP.PARTY_NUMBER
, HP.EMAIL_ADDRESS
, HP.PERSON_FIRST_NAME
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_NAME_SUFFIX
, HP.SALUTATION
, HP.PERSON_TITLE
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
, HP.PROVINCE
, HP.COUNTY
, HP.COUNTRY
, LANG. TERRITORY_SHORT_NAME COUNTRY_NAME
, HP.URL
, HPS.PARTY_SITE_ID
, HPS.PARTY_SITE_NAME
, HPS.REGION
, HPS.MAILSTOP
, HPS.LANGUAGE
, HPS.IDENTIFYING_ADDRESS_FLAG
, HCA.ACCOUNT_NUMBER
, HCA.ACCOUNT_REPLICATION_KEY
, HCA.ORIG_SYSTEM_REFERENCE
, HCA.STATUS
, HCA.CREATION_DATE
, HCA.CREATED_BY
, HCA.LAST_UPDATE_DATE
, HCA.LAST_UPDATED_BY
, HCA.LAST_UPDATE_LOGIN
, HCA.REQUEST_ID
, HCA.PROGRAM_ID
, HCA.PROGRAM_APPLICATION_ID
, HCA.PROGRAM_UPDATE_DATE
, HCA.WAREHOUSE_ID
, HCA.WH_UPDATE_DATE
, HCA.CUSTOMER_TYPE
, HCA.CUSTOMER_CLASS_CODE
, HCA.PRIMARY_SALESREP_ID
, HCA.SALES_CHANNEL_CODE
, HCA.TAX_CODE
, HCA.TAX_HEADER_LEVEL_FLAG
, HCA.TAX_ROUNDING_RULE
, HCA.FOB_POINT
, HCA.FREIGHT_TERM
, HCA.SHIP_PARTIAL
, HCA.SHIP_VIA
, HCA.PAYMENT_TERM_ID
, HCA.COTERMINATE_DAY_MONTH
, HCA.PRIMARY_SPECIALIST_ID
, HCA.SECONDARY_SPECIALIST_ID
, HCA.ACCOUNT_LIABLE_FLAG
, HCA.RESTRICTION_LIMIT_AMOUNT
, HCA.CURRENT_BALANCE
, HCA.PASSWORD_TEXT
, HCA.HIGH_PRIORITY_INDICATOR
, HCA.ACCOUNT_ESTABLISHED_DATE
, HCA.ACCOUNT_TERMINATION_DATE
, HCA.ACCOUNT_ACTIVATION_DATE
, HCA.CREDIT_CLASSIFICATION_CODE
, HCA.DEPARTMENT
, HCA.MAJOR_ACCOUNT_NUMBER
, HCA.HOTWATCH_SERVICE_FLAG
, HCA.HOTWATCH_SVC_BAL_IND
, HCA.HELD_BILL_EXPIRATION_DATE
, HCA.HOLD_BILL_FLAG
, HCA.HIGH_PRIORITY_REMARKS
, HCA.PO_EFFECTIVE_DATE
, HCA.PO_EXPIRATION_DATE
, HCA.REALTIME_RATE_FLAG
, HCA.SINGLE_USER_FLAG
, HCA.WATCH_ACCOUNT_FLAG
, HCA.WATCH_BALANCE_INDICATOR
, HCA.ACCT_LIFE_CYCLE_STATUS
, HCA.ACCOUNT_NAME
, HCA.DEPOSIT_REFUND_METHOD
, HCA.DORMANT_ACCOUNT_FLAG
, HCA.NPA_NUMBER
, HCA.PIN_NUMBER
, HCA.SUSPENSION_DATE
, HCA.WRITE_OFF_ADJUSTMENT_AMOUNT
, HCA.WRITE_OFF_PAYMENT_AMOUNT
, HCA.WRITE_OFF_AMOUNT
, HCA.SOURCE_CODE
, HCA.COMPETITOR_TYPE
, HCA.COMMENTS
, HCA.DATES_NEGATIVE_TOLERANCE
, HCA.DATES_POSITIVE_TOLERANCE
, HCA.DATE_TYPE_PREFERENCE
, HCA.OVER_SHIPMENT_TOLERANCE
, HCA.UNDER_SHIPMENT_TOLERANCE
, HCA.OVER_RETURN_TOLERANCE
, HCA.UNDER_RETURN_TOLERANCE
, HCA.ITEM_CROSS_REF_PREF
, HCA.SHIP_SETS_INCLUDE_LINES_FLAG
, HCA.ARRIVALSETS_INCLUDE_LINES_FLAG
, HCA.SCHED_DATE_PUSH_FLAG
, HCA.INVOICE_QUANTITY_RULE
, HCA.PRICING_EVENT
, HCA.STATUS_UPDATE_DATE
, HCA.AUTOPAY_FLAG
, HCA.NOTIFY_FLAG
, HCA.ATTRIBUTE_CATEGORY
, HCA.ATTRIBUTE1
, HCA.ATTRIBUTE2
, HCA.ATTRIBUTE3
, HCA.ATTRIBUTE4
, HCA.ATTRIBUTE5
, HCA.ATTRIBUTE6
, HCA.ATTRIBUTE7
, HCA.ATTRIBUTE8
, HCA.ATTRIBUTE9
, HCA.ATTRIBUTE10
, HCA.ATTRIBUTE11
, HCA.ATTRIBUTE12
, HCA.ATTRIBUTE13
, HCA.ATTRIBUTE14
, HCA.ATTRIBUTE15
, HCA.ATTRIBUTE16
, HCA.ATTRIBUTE17
, HCA.ATTRIBUTE18
, HCA.ATTRIBUTE19
, HCA.ATTRIBUTE20
, HCAS.CUSTOMER_CATEGORY_CODE
, HCA.CREATED_BY CREATED_BY_NAME
, HCA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, HCA.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
FROM HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCAS
, FND_TERRITORIES_TL LANG
WHERE HCA.STATUS = 'A'
AND HCA.PARTY_ID = HP.PARTY_ID
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND HPS.PARTY_ID(+) = HP.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HPS.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED'
AND HCAS.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND LANG. LANGUAGE = USERENV('LANG')
AND LANG.TERRITORY_CODE = HP.COUNTRY
AND HCA.LAST_UPDATE_DATE IN (SELECT MAX(B.LAST_UPDATE_DATE )
FROM HZ_CUST_ACCOUNTS B
WHERE B.PARTY_ID = HCA.PARTY_ID)

Columns

Name
CUST_ACCOUNT_ID
PARTY_ID
PARTY_NAME
PARTY_NUMBER
EMAIL_ADDRESS
PERSON_FIRST_NAME
PERSON_MIDDLE_NAME
PERSON_LAST_NAME
PERSON_NAME_SUFFIX
SALUTATION
PERSON_TITLE
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
POSTAL_CODE
PROVINCE
COUNTY
COUNTRY
COUNTRY_NAME
URL
PARTY_SITE_ID
PARTY_SITE_NAME
REGION
MAILSTOP
LANGUAGE
IDENTIFYING_ADDRESS_FLAG
ACCOUNT_NUMBER
ACCOUNT_REPLICATION_KEY
ORIG_SYSTEM_REFERENCE
STATUS
CREATION_DATE
CREATED_BY
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
REQUEST_ID
PROGRAM_ID
PROGRAM_APPLICATION_ID
PROGRAM_UPDATE_DATE
WAREHOUSE_ID
WH_UPDATE_DATE
CUSTOMER_TYPE
CUSTOMER_CLASS_CODE
PRIMARY_SALESREP_ID
SALES_CHANNEL_CODE
TAX_CODE
TAX_HEADER_LEVEL_FLAG
TAX_ROUNDING_RULE
FOB_POINT
FREIGHT_TERM
SHIP_PARTIAL
SHIP_VIA
PAYMENT_TERM_ID
COTERMINATE_DAY_MONTH
PRIMARY_SPECIALIST_ID
SECONDARY_SPECIALIST_ID
ACCOUNT_LIABLE_FLAG
RESTRICTION_LIMIT_AMOUNT
CURRENT_BALANCE
PASSWORD_TEXT
HIGH_PRIORITY_INDICATOR
ACCOUNT_ESTABLISHED_DATE
ACCOUNT_TERMINATION_DATE
ACCOUNT_ACTIVATION_DATE
CREDIT_CLASSIFICATION_CODE
DEPARTMENT
MAJOR_ACCOUNT_NUMBER
HOTWATCH_SERVICE_FLAG
HOTWATCH_SVC_BAL_IND
HELD_BILL_EXPIRATION_DATE
HOLD_BILL_FLAG
HIGH_PRIORITY_REMARKS
PO_EFFECTIVE_DATE
PO_EXPIRATION_DATE
REALTIME_RATE_FLAG
SINGLE_USER_FLAG
WATCH_ACCOUNT_FLAG
WATCH_BALANCE_INDICATOR
ACCT_LIFE_CYCLE_STATUS
ACCOUNT_NAME
DEPOSIT_REFUND_METHOD
DORMANT_ACCOUNT_FLAG
NPA_NUMBER
PIN_NUMBER
SUSPENSION_DATE
WRITE_OFF_ADJUSTMENT_AMOUNT
WRITE_OFF_PAYMENT_AMOUNT
WRITE_OFF_AMOUNT
SOURCE_CODE
COMPETITOR_TYPE
COMMENTS
DATES_NEGATIVE_TOLERANCE
DATES_POSITIVE_TOLERANCE
DATE_TYPE_PREFERENCE
OVER_SHIPMENT_TOLERANCE
UNDER_SHIPMENT_TOLERANCE
OVER_RETURN_TOLERANCE
UNDER_RETURN_TOLERANCE
ITEM_CROSS_REF_PREF
SHIP_SETS_INCLUDE_LINES_FLAG
ARRIVALSETS_INCLUDE_LINES_FLAG
SCHED_DATE_PUSH_FLAG
INVOICE_QUANTITY_RULE
PRICING_EVENT
STATUS_UPDATE_DATE
AUTOPAY_FLAG
NOTIFY_FLAG
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ATTRIBUTE6
ATTRIBUTE7
ATTRIBUTE8
ATTRIBUTE9
ATTRIBUTE10
ATTRIBUTE11
ATTRIBUTE12
ATTRIBUTE13
ATTRIBUTE14
ATTRIBUTE15
ATTRIBUTE16
ATTRIBUTE17
ATTRIBUTE18
ATTRIBUTE19
ATTRIBUTE20
CUSTOMER_CATEGORY_CODE
CREATED_BY_NAME
LAST_UPDATE_LOGIN_NAME
LAST_UPDATED_BY_NAME