Monday, 1 October 2012

Query For Account Hiererchy:

Step:1

create table XX_test_acc1
as select xx.parent_flex_value,xx.flex_value,xx.DESCRIPTION
from
(select DISTINCT ffvc.parent_flex_value,ffvc.flex_value,FFVC.DESCRIPTION
from
fnd_flex_value_children_v ffvc
where ffvc.summary_flag='N'
AND ffvc.flex_value_set_id=1014870
START WITH ffvc.parent_flex_value IS NOT NULL
CONNECT BY PRIOR ffvc.flex_value=ffvc.parent_flex_value
ORDER BY FFVC.PARENT_FLEX_VALUE) xx;

Step:2

select a.PARENT_FLEX_VALUE,a.DESCRIPTION,xx.parent_flex_value_low,xx.flex_value,xx.flex_value_meaning,xx.description
from
(SELECT   parent_flex_value_low,flex_value, flex_value_meaning, description
    FROM fnd_flex_values_vl
   WHERE (   ('' IS NULL)
          OR (structured_hierarchy_level IN (
                 SELECT hierarchy_id
                   FROM fnd_flex_hierarchies_vl h
                  WHERE h.flex_value_set_id = 1014871
                    AND h.hierarchy_name LIKE '')
             )
         )
     AND (flex_value_set_id = 1014871)
     AND enabled_flag='Y'
          AND (parent_flex_value_low in(select a.flex_value from cil_test_acc a))
ORDER BY parent_flex_value_low) xx ,XX_test_acc1 a
where xx.parent_flex_value_low=to_number(a.FLEX_VALUE);

No comments:

Post a Comment

Post a Comment