View: AMS_ORG_ACCT_V

Product:	AMS - Marketing
Description:	Customer account information
Implementation/DBA Data:	APPS.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
, HL.LANGUAGE 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
, HCSU.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
, NULL ACCOUNT_LIABLE_FLAG
, HCA.RESTRICTION_LIMIT_AMOUNT
, NULL CURRENT_BALANCE
, HCA.PASSWORD_TEXT
, HCA.HIGH_PRIORITY_INDICATOR
, HCA.ACCOUNT_ESTABLISHED_DATE
, NULL ACCOUNT_TERMINATION_DATE
, NULL ACCOUNT_ACTIVATION_DATE
, HCA.CREDIT_CLASSIFICATION_CODE
, NULL 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
, NULL REALTIME_RATE_FLAG
, HCA.SINGLE_USER_FLAG
, HCA.WATCH_ACCOUNT_FLAG
, HCA.WATCH_BALANCE_INDICATOR
, NULL ACCT_LIFE_CYCLE_STATUS
, HCA.ACCOUNT_NAME
, HCA.DEPOSIT_REFUND_METHOD
, NULL DORMANT_ACCOUNT_FLAG
, HCA.NPA_NUMBER
, HCA.PIN_NUMBER
, NULL SUSPENSION_DATE
, HCA.WRITE_OFF_ADJUSTMENT_AMOUNT
, HCA.WRITE_OFF_PAYMENT_AMOUNT
, HCA.WRITE_OFF_AMOUNT
, HCA.SOURCE_CODE
, NULL 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
, NULL 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_CUST_SITE_USES_ALL HCSU
, HZ_PARTIES HP
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCAS
, FND_TERRITORIES_TL LANG
, HZ_LOCATIONS HL
WHERE HCA.STATUS = 'A'
AND HCA.PARTY_ID = HP.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.STATUS='A'
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