oracle app-fnd-00798,用鍵彈性域實現物料分類編碼

EBS R12

定義值集INV-CATE

AA0000    大類名稱 0

AA0100    中類名稱 1

AA0101    小類名稱 2

....................

定義值集INV-CATE-BIG

值驗證類型：表(fnd_flex_values_vl)

值：SUBSTR(flex_value, 1, 2)

意義：description

hierarchy_level = 0

AND flex_value_set_id =(SELECT flex_value_set_id

FROM fnd_flex_value_sets WHERE flex_value_set_name = 'INV-CATE')

定義值集INV-CATE-MID

值驗證類型：表(fnd_flex_values_vl)

值：SUBSTR(flex_value, 3, 2)

意義：description

SUBSTR(flex_value, 1, 2) = :$FLEX$.INV-BIG

AND (hierarchy_level = 0 OR hierarchy_level = 1)

AND flex_value_set_id =(SELECT flex_value_set_id

FROM fnd_flex_value_sets WHERE flex_value_set_name = 'INV-CATE')

定義值集INV-CATE-SMA

值驗證類型：表(fnd_flex_values_vl)

值：SUBSTR(flex_value, 5, 2)

意義：description

WHERE (SUBSTR(flex_value, 1, 2) = :$FLEX$.INV-BIG

AND SUBSTR(flex_value, 3, 2) = :$FLEX$.INV-MID

AND hierarchy_level = '2')

OR (SUBSTR(flex_value, 1, 2) = :$FLEX$.INV-BIG AND :$FLEX$.INV-MID='00' AND hierarchy_level = '0')

OR (SUBSTR(flex_value, 1, 2) = :$FLEX$.INV-BIG AND SUBSTR(flex_value, 3, 2)=:$FLEX$.INV-MID AND hierarchy_level = '1')

AND flex_value_set_id =(SELECT flex_value_set_id

FROM fnd_flex_value_sets WHERE flex_value_set_name = 'INV-CATE')

在一个环境测试成功，在另一个环境测试遇到问题

APP-FND-00668: The data that defines the flexfield on this field may be inconsistent. Inform your system administrator that the function: FDFRKS could not find the structure definition for the flexfield specified by Application = &APPL, Code = MCAT and Structure number = 50448 (APPID=401)

--------------------------------------------------------------

APP-FND-00798: Invalid reference CUX in value set attached to segment INV_MID

Please check the WHERE clauses of your table validated value sets.

值集被使用，引起的問題。問題完全解決。

設置步驟：

1.設置值集(應用開發員職責)

2.設置鍵彈性域，并編譯(應用開發員職責)

3.設置料號的分類代碼、分類集(倉庫管理超級用戶職責)必须先设置分类代码，然后才能设置分类集

2010/09/16

现在又遇到类似的问题，Search for the Profile option: Flexfields: Validate on Server，不知这个设置是否会影响。

Based on "Unable To Use $FLEX$ In Table Name Field While Creating Value Set (Doc ID 1071197.1)“：

"Using $FLEX$ in the field Table Name of a Value Set does not work because there is a validation, at database level, of the table and column used in the Define Valueset forms.“

:$FLEX$.Value_ Set_Name的用法：Value_ Set_Name是同一个弹性域或参数窗口的前一段的值集名称或段名称，区分大小写，且只能包含字母、数字、下划线。

Using bind variables in WHERE/ORDER BY clauses

You may use special bind variables, such as :block.field, :$PROFILES$.Option_name, or

:$FLEX$.Value_set_name, in your WHERE/ORDER BY clause. However, you may not

use them in the Value Column or Hidden ID Column fields (where you would normally

specify a column name), even if you do specify a SQL fragment instead of specifying a

single column name. You may use bind variables in the Description Column and

Additional Columns fields.

Important: If you are using flexfields server-side validation, you cannot

use form field references (:block.field). You must either remove your

field references or turn off flexfields server-side validation using the

profile option Flexfields:Validate on Server.

If you are using a validation table with special arguments such as :$FLEX$.Value_Set_Name for your value set, you should specify No Security in the Security Typefield, since any security rules you have for your value set would ignore the values ofthese special arguments, and your rules could have effects other than what you intend.