?

1，父值和子值常用表

fnd_flex_value_norm_hierarchy-------父值，其中有子值的范圍，

fnd_flex_value_children_v--------------可以查詢 父值下包含的所有子值。

以上兩張表都通過(guò)flex_value_set_id做定位

應(yīng)用：在報(bào)表中存在一類參數(shù)情況，要求參數(shù)輸入父值的時(shí)候，返回全部子值的相應(yīng)信息，此要求的實(shí)現(xiàn)關(guān)鍵語(yǔ)法如下：

-------先驗(yàn)證是否是父值（fnd_flex_value_norm_hierarchy）

?select count(1)
??? into l_count
??? from fnd_flex_value_norm_hierarchy ffh,
???????? fnd_id_flex_segments????????? ffs,
???????? gl_sets_of_books????????????? gb
??? where ffh.flex_value_set_id = ffs.flex_value_set_id
??? and gb.short_name = 'PROPERTY_SOB'----------------賬簿簡(jiǎn)稱
??? and ffs.application_id = 101
??? and ffs.id_flex_code = 'GL#'
??? and ffs.id_flex_num = gb.chart_of_accounts_id
??? and ffs.application_column_name = 'SEGMENT1'--------公司段可以換為其他段
??? and ffh.parent_flex_value = g_company;-------------參數(shù)
?
??? if l_count > 0-----------------------------------------說(shuō)明是父值
??? then
?
????????? SELECT gb.name,ffh.flex_value_set_id, ffh.parent_flex_value,ffh.child_flex_value_low, ffh.child_flex_value_high,ffc.flex_value

-----------可以找出相應(yīng)的子值范圍或者父值中包含的所有子值（ffc.flex_value）
?
????? from fnd_flex_value_norm_hierarchy ffh,
?????????? fnd_id_flex_segments????????? ffs,
?????????? gl_sets_of_books????????????? gb,
?????????? fnd_flex_value_children_v? ffc
????? where ffh.flex_value_set_id = ffs.flex_value_set_id
????? AND ffc.flex_value_set_id=ffh.flex_value_set_id
????? AND ffc.parent_flex_value=ffh.parent_flex_value
?? /*?? and gb.short_name = 'PROPERTY_SOB'*/
????? and ffs.application_id = 101
????? and ffs.id_flex_code = 'GL#'
????? and ffs.id_flex_num = gb.chart_of_accounts_id
????? and ffs.application_column_name = 'SEGMENT1'
????? /*and ffh.parent_flex_value = g_company*/;

??? else
???? 直接按子值，最簡(jiǎn)單的情況處理即可

?? ?
??? end if;
?

?2，,常用查詢段值 FLEX_VALUE_SET_ID或者FLEX_VALUE如下：

?????? select ffv.DESCRIPTION ,ffv.FLEX_VALUE_SET_ID from? fnd_flex_values_vl ffv ,fnd_id_flex_segments ffs,gl_sets_of_books gb
????? ?
?????? where ffv.FLEX_VALUE_SET_ID=ffs.FLEX_VALUE_SET_ID
?????? and gb.SHORT_NAME='PROPERTY_SOB'---------------------------賬簿簡(jiǎn)稱
?????? and ffv.FLEX_VALUE='04'----------------------------------------具體值
?????? and ffs.APPLICATION_ID=101
?????? and ffs.ID_FLEX_CODE='GL#'
?????? and ffs.ID_FLEX_NUM= gb.CHART_OF_ACCOUNTS_ID
?????? and ffs.APPLICATION_COLUMN_NAME='SEGMENT1'; --------段
??????

?

轉(zhuǎn)載于:https://www.cnblogs.com/akami/p/6933636.html

總結(jié)

以上是生活随笔為你收集整理的段值相关的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。