1. Tables and Views for Sales and Fusion Service
  2. ZSP_ODM_CUST_V

ZSP_ODM_CUST_V

Details

  • Schema: FUSION

  • Object owner: ZSP

  • Object type: VIEW

Columns

Name

SALES_ACCOUNT_ID

CUSTOMER_WID

CUST_SIZE_CODE

CUST_ANNL_REVENUE

CUST_NEXT_YEAR_POT_REVN

CUST_ORG_TYPE_NAME

CUST_NO_OF_EMPLOYEES

CUST_SALES_ACCT_FLAG

CUST_DB_RATING

CUST_ACCT_STATUS

CUST_ACCT_TYPE

CUST_COMPETITOR_FLAG

CUST_COUNTRY

CUST_SIC_CODE

CUST_LINE_OF_BUSINESS

CUST_CURR_YEAR_POT_REVN

CUST_SMALL_BUSS_IND

CUST_PARTNER_FLAG

CUST_PARTY_TYPE_CODE

CUST_POT_SALES_VOL

CUST_PROSPECT_FLAG

CUST_PUBLIC_FLAG

CUST_REV_GROWTH_CAT

CUST_STATE

CUST_INDUST_NAME

Query

SQL_Statement

SELECT

SALES_ACCOUNT.sales_account_id,

PARTY.PARTY_ID customer_wid,

PARTY_ORG_PROFILES.ORGANIZATION_SIZE CUST_SIZE_CODE,

PARTY.CURR_FY_POTENTIAL_REVENUE CUST_ANNL_REVENUE,

PARTY.NEXT_FY_POTENTIAL_REVENUE CUST_NEXT_YEAR_POT_REVN,

(SELECT LOOKUP.MEANING

FROM HZ_CODE_ASSIGNMENTS ASSGN,

FND_LOOKUP_VALUES_TL LOOKUP

WHERE ASSGN.owner_table_id = PARTY.PARTY_ID

AND ASSGN.owner_table_name = 'HZ_PARTIES'

AND ASSGN.CLASS_CATEGORY = 'ORGANIZATION_TYPE'

AND ASSGN.CLASS_CATEGORY = LOOKUP.LOOKUP_TYPE

AND ASSGN.CLASS_CODE = LOOKUP.LOOKUP_CODE

AND LOOKUP.LANGUAGE = 'US'

AND ASSGN.STATUS = 'A'

AND ASSGN.PRIMARY_FLAG = 'Y'

) CUST_ORG_TYPE_NAME,

PARTY.EMPLOYEES_TOTAL CUST_NO_OF_EMPLOYEES,

(

CASE

WHEN (SELECT COUNT(1)

FROM HZ_PARTY_USG_ASSIGNMENTS ap

WHERE PARTY.party_id = ap.party_id

AND ap.party_usage_code = 'SALES_ACCOUNT') >= 1

THEN 'Y'

ELSE 'N'

END ) CUST_SALES_ACCT_FLAG,

PARTY_ORG_PROFILES.DB_RATING CUST_DB_RATING,

PARTY.status CUST_ACCT_STATUS,

PARTY.party_type CUST_ACCT_TYPE,

( DECODE(

(SELECT party_usage_code FROM hz_party_usg_assignments WHERE PARTY.party_id=hz_party_usg_assignments.party_id

AND party_usage_code ='COMPETITOR'

),NULL,'N','Y') ) CUST_COMPETITOR_FLAG,

PARTY.COUNTRY CUST_COUNTRY,

PARTY.SIC_CODE CUST_SIC_CODE,

PARTY_ORG_PROFILES.LINE_OF_BUSINESS CUST_LINE_OF_BUSINESS,

PARTY.CURR_FY_POTENTIAL_REVENUE CUST_CURR_YEAR_POT_REVN,

PARTY_ORG_PROFILES.SMALL_BUS_IND CUST_SMALL_BUSS_IND,

PARTY.TRADING_PARTNER_IDENTIFIER CUST_PARTNER_FLAG,

PARTY.PARTY_TYPE CUST_PARTY_TYPE_CODE,

PARTY.NEXT_FY_POTENTIAL_REVENUE CUST_POT_SALES_VOL,

(DECODE(

(SELECT party_usage_code FROM hz_party_usg_assignments WHERE PARTY.party_id=hz_party_usg_assignments.party_id

AND party_usage_code ='PROSPECT'

),NULL,'N','Y') ) CUST_PROSPECT_FLAG,

PARTY_ORG_PROFILES.public_private_ownership_flag CUST_PUBLIC_FLAG,

((PARTY.next_fy_potential_revenue- (

CASE

WHEN PARTY.curr_fy_potential_revenue = 0

OR PARTY.curr_fy_potential_revenue IS NULL

THEN PARTY.next_fy_potential_revenue

ELSE PARTY.curr_fy_potential_revenue

END ) )*100 / (

CASE

WHEN PARTY.curr_fy_potential_revenue = 0

OR PARTY.curr_fy_potential_revenue IS NULL

THEN (

CASE

WHEN PARTY.next_fy_potential_revenue = 0

THEN 1

ELSE PARTY.next_fy_potential_revenue

END )

ELSE PARTY.curr_fy_potential_revenue

END ) ) CUST_REV_GROWTH_CAT,

PARTY.STATE CUST_STATE,

(SELECT CLASS_CATEGORY

||'~'

||CLASS_CODE

FROM ZCA_BI_CODE_ASSIGNMENTS_V

WHERE owner_table_id = PARTY.PARTY_ID

AND owner_table_name = 'HZ_PARTIES'

AND PROFILE_OPTION_NAME = 'MOT_INDUSTRY_CLASS_CATEGORY'

AND STATUS = 'A'

AND LEVEL_NAME = 'SITE'

AND PRIMARY_FLAG = 'Y'

) CUST_INDUST_NAME

FROM HZ_PARTIES PARTY,

(SELECT *

FROM HZ_ORGANIZATION_PROFILES

WHERE (TRUNC(sysdate) between effective_start_date and effective_end_date)

) PARTY_ORG_PROFILES,

ZCA_SALES_ACCOUNTS SALES_ACCOUNT

WHERE PARTY.party_id = PARTY_ORG_PROFILES.party_id

AND PARTY.party_id = SALES_ACCOUNT.PARTY_ID