value xxx for the flexfield segment customer:does not exist in the value set xxxx

value xxx(customer_name) for the flexfield segment customer:does not exist in the value set xxxx(OM_CUST_NAME)

一開始懷疑是VALUE SET的SEGMENT長度不夠.將60放到240.

此時點LOV選的CUSTOMER NAME時,上面的提示依然出現,但用COPY的方式輸 入CUSTOMER NAME就正常(不過沒有資料產生).

[@more@]

在TOAD用SQL利用CUSTOEMR_NUMBER產生有資料,用CUSTOMER NAME就沒資料了.

加TRIM(),即TRIM(CUSTOMER_NAME)=TRIM(xxxx).這時資料又有了.

原來AR_CUSTOMERS這VIEW中的CUSTOMER NAME後面有一個空格' '.為了不改REPORT中SQL.故改了AR_CUSTOMERS的VIEW.

下面黑斜體部分,原因沒有TRIM()的.

CREATE OR REPLACE FORCE VIEW APPS.AR_CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_KEY, STATUS,
ORIG_SYSTEM_REFERENCE, CUSTOMER_PROSPECT_CODE, CUSTOMER_CATEGORY_CODE, CUSTOMER_CLASS_CODE, CUSTOMER_TYPE,
PRIMARY_SALESREP_ID, SIC_CODE, TAX_REFERENCE, TAX_CODE, FOB_POINT,
SHIP_VIA, GSA_INDICATOR, SHIP_PARTIAL, TAXPAYER_ID, PRICE_LIST_ID,
FREIGHT_TERM, ORDER_TYPE_ID, SALES_CHANNEL_CODE, WAREHOUSE_ID, MISSION_STATEMENT,
NUM_OF_EMPLOYEES, POTENTIAL_REVENUE_CURR_FY, POTENTIAL_REVENUE_NEXT_FY, FISCAL_YEAREND_MONTH, YEAR_ESTABLISHED,
ANALYSIS_FY, COMPETITOR_FLAG, REFERENCE_USE_FLAG, THIRD_PARTY_FLAG, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE,
CUSTOMER_NAME_PHONETIC, TAX_HEADER_LEVEL_FLAG, TAX_ROUNDING_RULE, GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20)
AS
SELECT CUST.CUST_ACCOUNT_ID CUSTOMER_ID , trim(SUBSTRB(PARTY.PARTY_NAME,1,50)) CUSTOMER_NAME , CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER , PARTY.CUSTOMER_KEY CUSTOMER_KEY , CUST.STATUS STATUS , CUST.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE , 'CUSTOMER' CUSTOMER_PROSPECT_CODE , PARTY.CATEGORY_CODE CUSTOMER_CATEGORY_CODE , CUST.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE , CUST.CUSTOMER_TYPE CUSTOMER_TYPE , CUST.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.SIC_CODE, NULL) SIC_CODE , PARTY.TAX_REFERENCE TAX_REFERENCE , CUST.TAX_CODE TAX_CODE , CUST.FOB_POINT FOB_POINT , CUST.SHIP_VIA SHIP_VIA , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.GSA_INDICATOR_FLAG, 'N') GSA_INDICATOR , CUST.SHIP_PARTIAL SHIP_PARTIAL , PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID , CUST.PRICE_LIST_ID PRICE_LIST_ID , CUST.FREIGHT_TERM FREIGHT_TERM , CUST.ORDER_TYPE_ID ORDER_TYPE_ID , CUST.SALES_CHANNEL_CODE SALES_CHANNEL_CODE , CUST.WAREHOUSE_ID WAREHOUSE_ID , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.MISSION_STATEMENT, NULL) MISSION_STATEMENT , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.EMPLOYEES_TOTAL, TO_NUMBER(NULL)) NUM_OF_EMPLOYEES , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.CURR_FY_POTENTIAL_REVENUE, TO_NUMBER(NULL)) POTENTIAL_REVENUE_CURR_FY , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.NEXT_FY_POTENTIAL_REVENUE, TO_NUMBER(NULL)) POTENTIAL_REVENUE_NEXT_FY , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.FISCAL_YEAREND_MONTH, NULL) FISCAL_YEAREND_MONTH , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.YEAR_ESTABLISHED, TO_NUMBER(NULL)) YEAR_ESTABLISHED , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.ANALYSIS_FY, NULL) ANALYSIS_FY , PARTY.COMPETITOR_FLAG COMPETITOR_FLAG , PARTY.REFERENCE_USE_FLAG REFERENCE_USE_FLAG , PARTY.THIRD_PARTY_FLAG THIRD_PARTY_FLAG , CUST.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , CUST.ATTRIBUTE1 ATTRIBUTE1 , CUST.ATTRIBUTE2 ATTRIBUTE2 , CUST.ATTRIBUTE3 ATTRIBUTE3 , CUST.ATTRIBUTE4 ATTRIBUTE4 , CUST.ATTRIBUTE5 ATTRIBUTE5 , CUST.ATTRIBUTE6 ATTRIBUTE6 , CUST.ATTRIBUTE7 ATTRIBUTE7 , CUST.ATTRIBUTE8 ATTRIBUTE8 , CUST.ATTRIBUTE9 ATTRIBUTE9 , CUST.ATTRIBUTE10 ATTRIBUTE10 , CUST.ATTRIBUTE11 ATTRIBUTE11 , CUST.ATTRIBUTE12 ATTRIBUTE12 , CUST.ATTRIBUTE13 ATTRIBUTE13 , CUST.ATTRIBUTE14 ATTRIBUTE14 , CUST.ATTRIBUTE15 ATTRIBUTE15 , CUST.LAST_UPDATED_BY LAST_UPDATED_BY , CUST.LAST_UPDATE_DATE LAST_UPDATE_DATE , CUST.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , CUST.CREATED_BY CREATED_BY , CUST.CREATION_DATE CREATION_DATE , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.ORGANIZATION_NAME_PHONETIC,NULL) CUSTOMER_NAME_PHONETIC , CUST.TAX_HEADER_LEVEL_FLAG TAX_HEADER_LEVEL_FLAG , CUST.TAX_ROUNDING_RULE TAX_ROUNDING_RULE , CUST.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY , CUST.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1 , CUST.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2 , CUST.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3 , CUST.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4 , CUST.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5 , CUST.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6 , CUST.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7 , CUST.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8 , CUST.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9 , CUST.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10 , CUST.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11 , CUST.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12 , CUST.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13 , CUST.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14 , CUST.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15 , CUST.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16 , CUST.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17 , CUST.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18 , CUST.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19 , CUST.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20 FROM HZ_CUST_ACCOUNTS CUST, HZ_PARTIES PARTY WHERE cust.party_id = party.party_id;