FND Design Data [Home] [Help]

View: AMS_ACCT_INTEREST_V

Product: AMS - Marketing
Description: This view is used by Discoverer Business Area:List generation Folders.It is a user view of Customer and sites
Implementation/DBA Data: ViewAPPS.AMS_ACCT_INTEREST_V
View Text

SELECT CUS.PARTY_ID CUSTOMER_ID
, CUS.PARTY_NAME CUSTOMER_NAME
, CUS.PARTY_NUMBER CUSTOMER_NUMBER
, CUS.CURR_FY_POTENTIAL_REVENUE ANNUAL_REVENUE
, CUS.EMPLOYEES_TOTAL NUM_OF_EMPLOYEES
, CUS.CATEGORY_CODE CUSTOMER_CATEGORY_CODE
, TO_CHAR(TO_DATE(CUS.FISCAL_YEAREND_MONTH
, 'MM')
, 'MM FMMONTH') FISCAL_YEAR_END
, DECODE(CUS.TOTAL_NUM_OF_ORDERS
, NVL(CUS.TOTAL_NUM_OF_ORDERS
, 0)
, 'PROSPECT'
, 'CUSTOMER')CUSTOMER_PROSPECT_CODE
, DECODE(CUS.STATUS
, 'A'
, 'ACTIVE'
, 'INACTIVE') CUSTOMER_ACTIVE
, NVL(CUS.REFERENCE_USE_FLAG
, 'N') REFERENCE
, NVL(CUS.COMPETITOR_FLAG
, 'N') COMPETITOR
, NVL(CUS.THIRD_PARTY_FLAG
, 'N') PARTNER
, DECODE(CUS.DO_NOT_MAIL_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'
, 'Y') OK_TO_MAIL
, SIT.CUST_ACCT_SITE_ID ADDRESS_ID
, NVL(SIT.KEY_ACCOUNT_FLAG
, 'N') KEY_SITE
, DECODE(SIT.STATUS
, 'A'
, 'ACTIVE'
, 'INACTIVE') SITE_ACTIVE
, LOC.CITY
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.COUNTY
, LOC.COUNTRY
, TER.TERRITORY_SHORT_NAME COUNTRY_NAME
, SIT.ORG_ID ORG_ID
, ORG.NAME ORG_NAME
, SIT.LAST_UPDATED_BY SIT_LAST_UPDATED_BY
, SIT.LAST_UPDATE_DATE SIT_LAST_UPDATE_DATE
, FND_USER_AP_PKG.GET_USER_NAME(SIT.LAST_UPDATED_BY) SIT_LAST_UPDATED_NAME
, CUS.LAST_UPDATED_BY CUS_LAST_UPDATED_BY
, CUS.LAST_UPDATE_DATE CUS_LAST_UPDATE_DATE
, FND_USER_AP_PKG.GET_USER_NAME(CUS.LAST_UPDATED_BY) CUS_LAST_UPDATED_NAME
, AIT.INTEREST_TYPE SITE_INTEREST_TYPE
, AIT.INTEREST_TYPE||'/'||AICP.CODE SITE_PRIMARY_INT_CODE
, AIT.INTEREST_TYPE||'/'||AICP.CODE||'/'||AICS.CODE SITE_SECONDARY_INT_CODE
, AICP.CODE SITE_PRIMARY_CODE
, AICS.CODE SITE_SECONDARY_CODE
FROM HZ_PARTIES CUS
, HZ_CUST_ACCOUNTS CUSTACCT
, HZ_CUST_ACCT_SITES_ALL SIT
, HZ_PARTY_SITES PARTYSITES
, HZ_LOCATIONS LOC
, FND_TERRITORIES_VL TER
, AS_INTERESTS_ALL AIA
, AS_INTEREST_TYPES_VL AIT
, AS_INTEREST_CODES_VL AICP
, AS_INTEREST_CODES_VL AICS
, HR_OPERATING_UNITS ORG
WHERE CUS.PARTY_ID= CUSTACCT.PARTY_ID
AND CUSTACCT.CUST_ACCOUNT_ID = (SELECT MIN(C1.CUST_ACCOUNT_ID)
FROM HZ_CUST_ACCOUNTS C1
WHERE CUSTACCT.PARTY_ID = C1.PARTY_ID)
AND CUS.PARTY_ID = PARTYSITES.PARTY_ID
AND CUSTACCT.CUST_ACCOUNT_ID =SIT.CUST_ACCOUNT_ID
AND SIT.PARTY_SITE_ID = PARTYSITES.PARTY_SITE_ID
AND PARTYSITES.LOCATION_ID =LOC.LOCATION_ID
AND LOC.COUNTRY = TER.TERRITORY_CODE
AND SIT.ORG_ID = ORG.ORGANIZATION_ID
AND AIA.CUSTOMER_ID(+) = PARTYSITES.PARTY_ID
AND AIA.ADDRESS_ID(+) = PARTYSITES.PARTY_SITE_ID
AND AIA.INTEREST_TYPE_ID = AIT.INTEREST_TYPE_ID(+)
AND AIT.COMPANY_CLASSIFICATION_FLAG(+) = 'Y'
AND AIA.PRIMARY_INTEREST_CODE_ID = AICP.INTEREST_CODE_ID(+)
AND AIA.SECONDARY_INTEREST_CODE_ID = AICS.INTEREST_CODE_ID(+)
AND SIT.CUST_ACCT_SITE_ID > 0

Columns

Name
CUSTOMER_ID
CUSTOMER_NAME
CUSTOMER_NUMBER
ANNUAL_REVENUE
NUM_OF_EMPLOYEES
CUSTOMER_CATEGORY_CODE
FISCAL_YEAR_END
CUSTOMER_PROSPECT_CODE
CUSTOMER_ACTIVE
REFERENCE
COMPETITOR
PARTNER
OK_TO_MAIL
ADDRESS_ID
KEY_SITE
SITE_ACTIVE
CITY
STATE
POSTAL_CODE
COUNTY
COUNTRY
COUNTRY_NAME
ORG_ID
ORG_NAME
SIT_LAST_UPDATED_BY
SIT_LAST_UPDATE_DATE
SIT_LAST_UPDATED_NAME
CUS_LAST_UPDATED_BY
CUS_LAST_UPDATE_DATE
CUS_LAST_UPDATED_NAME
SITE_INTEREST_TYPE
SITE_PRIMARY_INT_CODE
SITE_SECONDARY_INT_CODE
SITE_PRIMARY_CODE
SITE_SECONDARY_CODE