oracle ebs r12 报表的 Oracle flex 值层次结构 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19998984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle flex value hierarchy SQL query for oracle ebs r12 report
提问by user2995784
I am new to oracle and oracle ebs and I need some help.
我是 oracle 和 oracle ebs 的新手，我需要一些帮助。
I am doing a report in oracle ebs r12 and I need to list flex values from fnd_flex_values_vl view in a hierarchical way using a SQL query. It doesn`t necessary has to be a hierarchial query. Any query will do. I just need a SQL statement that will return me flex values in their hierarchial way.
我正在 oracle ebs r12 中做一个报告，我需要使用 SQL 查询以分层方式从 fnd_flex_values_vl 视图中列出 flex 值。不一定非得是分层查询。任何查询都可以。我只需要一个 SQL 语句，它将以层次结构的方式返回给我 flex 值。
There are two objects, that store information about flex values hierarchy. It is FND_FLEX_VALUE_NORM_HIERARCHY (table) and fnd_flex_value_children_v (view). I assume one of these is enough, since fnd_flex_value_children_v is made using FND_FLEX_VALUE_NORM_HIERARCHY and some other objects.
有两个对象，用于存储有关 flex 值层次结构的信息。它是 FND_FLEX_VALUE_NORM_HIERARCHY（表）和 fnd_flex_value_children_v（视图）。我认为其中之一就足够了，因为 fnd_flex_value_children_v 是使用 FND_FLEX_VALUE_NORM_HIERARCHY 和其他一些对象制作的。
However, the problem I faced is, that several parents may be listed for one flex value and that I need to find all top parents or leaves in order to do an up-bottom or bottom-up hierarchy. As far as I understand fnd_flex_value_children_v doesn`t necessary store top parents (stores only children).
但是，我面临的问题是，可能会为一个 flex 值列出多个父级，并且我需要找到所有顶级父级或叶子，以便进行自上而下或自下而上的层次结构。据我了解 fnd_flex_value_children_v 不需要存储顶级父母（仅存储孩子）。
Also it seems that there is probably not one, but there may be multiple hierarchies (if so, I need to list them all in one query).
此外，似乎可能没有，但可能有多个层次结构（如果是这样，我需要在一个查询中将它们全部列出）。
Your help will be really appreciated. I`ve been struggling with this one quite a while. Thank you very much for your attention.
您的帮助将不胜感激。我一直在努力解决这个问题。非常感谢您的关注。
Best regards, new user. =)
最好的问候，新用户。=)
回答by Patrick Bacon
You should use the table, APPLSYS.FND_FLEX_VALUE_SETS. The objects you identify are metadata objects about the FND_FLEX_VALUE_SETS table.
您应该使用表 APPLSYS.FND_FLEX_VALUE_SETS。您标识的对象是有关 FND_FLEX_VALUE_SETS 表的元数据对象。
I like to start with the root record.
我喜欢从根记录开始。
Here is my method to find root records (no parent).
这是我查找根记录（无父记录）的方法。
SELECT DISTINCT
FVS.PARENT_FLEX_VALUE_SET_ID
FROM
APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE
FVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL
ORDER BY 1 ;
Once I find the root record, I develop my start by clause:
一旦我找到根记录，我就开发我的 start by 子句：
START WITH
(
FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL
)
This clause does capture all root records (you could select just one).
该子句确实捕获了所有根记录（您可以只选择一个）。
Next, I develop my connect by clause. Since I want my hierarchy to start at the root, I would take this approach:
接下来，我开发我的 connect by 子句。由于我希望我的层次结构从根开始，我会采用这种方法：
level 1 flex_value_set_id ....prior level
级别 1 flex_value_set_id ....先前级别
level 2 parent_flex_value_set_id
级别 2 parent_flex_value_set_id
CONNECT BY fvs.parent_flex_value_set_id = prior fvs.flex_value_set_id ;
This results in this statement:
这导致以下语句：
SELECT LEVEL,
FVS.*
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
START WITH
(
FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.parent_flex_value_set_id IS NOT NULL
)
)
CONNECT BY FVS.PARENT_FLEX_VALUE_SET_ID = PRIOR FVS.FLEX_VALUE_SET_ID ;
One then can add the flex values as follows:
然后可以按如下方式添加 flex 值：
SELECT
LEVEL,
FVS.*
FROM
(SELECT
FLEX.FLEX_VALUE_SET_ID,
FLEX.PARENT_FLEX_VALUE_SET_ID,
FLEX.FLEX_VALUE_SET_NAME,
FVAL.FLEX_VALUE
FROM
APPLSYS.FND_FLEX_VALUE_SETS FLEX,
APPLSYS.FND_FLEX_VALUES FVAL
WHERE
FLEX.FLEX_VALUE_SET_ID = FVAL.FLEX_VALUE_SET_ID(+)) FVS
START WITH
(FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT
FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.parent_flex_value_set_id IS NOT NULL ) )
CONNECT BY
FVS.PARENT_FLEX_VALUE_SET_ID = PRIOR FVS.FLEX_VALUE_SET_ID;
回答by Mauricio Ch
May be this can help u
可能这可以帮助你
SELECT fvc.PARENT_FLEX_VALUE RUBRO_N0 ,FVT.description
DESC_RUBRO_N0,FVC.FLEX_VALUE RUBRO_N1 , fvc.DESCRIPTION
DESC_RUBRO_N1,FVC2.FLEX_VALUE RUBRO_N2 , FVC2.DESCRIPTION
DESC_RUBRO_N2,FVC3.FLEX_VALUE RUBRO_N3 , FVC3.DESCRIPTION
DESC_RUBRO_N3,FVC4.FLEX_VALUE RUBRO_N4 , FVC4.DESCRIPTION
DESC_RUBRO_N4,NVL(FVC4.FLEX_VALUE,NVL(FVC3.FLEX_VALUE,NVL(FVC2.FLEX_VALUE,FVC.FLEX_VALUE))) RUBROFIN
FROM FND_FLEX_VALUE_CHILDREN_V fvc
,FND_FLEX_VALUE_CHILDREN_V FVC2
,FND_FLEX_VALUE_CHILDREN_V FVC3
,FND_FLEX_VALUE_CHILDREN_V FVC4
,FND_FLEX_VALUES_TL FVT
WHERE fvc.FLEX_VALUE_SET_ID = 1016176
AND fvc.PARENT_FLEX_VALUE not in(SELECT FLEX_VALUE FROM FND_FLEX_VALUE_CHILDREN_V WHERE FLEX_VALUE_SET_ID = --YOUR FLEX_VALUE_SET_ID)
AND fvc.FLEX_VALUE = FVC2.PARENT_FLEX_VALUE (+)
AND fvc2.FLEX_VALUE = FVC3.PARENT_FLEX_VALUE (+)
AND fvc3.FLEX_VALUE = FVC4.PARENT_FLEX_VALUE (+)
AND fvc.PARENT_FLEX_VALUE = FVT.FLEX_VALUE_MEANING
AND FVT.SOURCE_LANG = 'ESA'
AND FVT.LANGUAGE = 'ESA' AND FVT.LAST_UPDATE_LOGIN NOT IN (0)
ORDER BY 1,2,3,5,7
;
Best Regards
此致
回答by Andy Haack
This SQL from the Blitz Report library returns the hierarchy structure based on fnd_flex_value_norm_hierarchy with all containing child flex values: https://www.enginatics.com/reports/fnd-flex-value-hierarchy/
来自 Blitz Report 库的这个 SQL 返回基于 fnd_flex_value_norm_hierarchy 的层次结构，所有包含子 flex 值：https://www.enginatics.com/reports/fnd-flex-value-hierarchy/
select
lpad(' ',2*(level-1))||level level_,
lpad(' ',2*(level-1))||ffvnh.parent_flex_value value,
ffvv.description,
decode(ffvnh.range_attribute,'P','Parent','C','Child') range_attribute,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
decode(connect_by_isleaf,1,'Yes') is_leaf,
connect_by_root ffvnh.parent_flex_value root_value,
substr(sys_connect_by_path(ffvnh.parent_flex_value,'-> '),4) path,
ffvnh.parent_flex_value value_flat
from
(
select
ffvnh.parent_flex_value,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.range_attribute,
ffvnh.flex_value_set_id
from
fnd_flex_value_norm_hierarchy ffvnh
where
ffvnh.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name)
union all
select
ffv2.flex_value parent_flex_value,
null child_flex_value_low,
null child_flex_value_high,
'x' range_attribute,
ffv2.flex_value_set_id
from
fnd_flex_values ffv2
where
2=2 and
ffv2.summary_flag='N' and
ffv2.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name)
) ffvnh,
fnd_flex_values_vl ffvv
where
3=3 and
ffvnh.parent_flex_value=ffvv.flex_value and
ffvnh.flex_value_set_id=ffvv.flex_value_set_id
connect by nocycle
ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag
start with
ffvnh.parent_flex_value=:parent_flex_value and
1=1 and
(:parent_flex_value is not null or
not exists (select null from
fnd_flex_value_norm_hierarchy ffvnh0
where
ffvnh0.flex_value_set_id=(select ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:flex_value_set_name) and
ffvnh.parent_flex_value between ffvnh0.child_flex_value_low and ffvnh0.child_flex_value_high and
ffvv.summary_flag=decode(ffvnh0.range_attribute,'P','Y','N')
)
)
To see all hierarchies (different parent top values), remove the 'start with' restriction to the parent flex value:
要查看所有层次结构（不同的父顶部值），请删除对父 flex 值的“开始于”限制：
ffvnh.parent_flex_value=:parent_flex_value
