VIEW: APPS.AMS_ACCT_INTEREST_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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