Chart of Account Segment Hierarchy Ranges

GL : Chart of Account Segment Hierarchy Ranges:-
----------------------------------------------------------

Chart of account segment hierarchy ranges and attributes for parent accounts

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

I have given the Chart of account segment name as 'ABC' and Parent segment Value as XYZ. Check out the Values In your Instances and run accordingly.

SELECT FVS.FLEX_VALUE_SET_NAME"Value Set"
, FV.FLEX_VALUE
, NH.PARENT_FLEX_VALUE "Parent"
, FVT.DESCRIPTION
, NH.RANGE_ATTRIBUTE "Inc C or P?"
, NH.CHILD_FLEX_VALUE_LOW "From"
, NH.CHILD_FLEX_VALUE_HIGH "To"
, NH.PARENT_FLEX_VALUE || ' : ' ||NH.RANGE_ATTRIBUTE || ' : ' ||
NH.CHILD_FLEX_VALUE_LOW || ' -> ' ||NH.CHILD_FLEX_VALUE_HIGH "Hierarchy Range"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,1,1)"Posting"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,3,1)"Budgeting"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,5,1)"Acc Type"
, FV.ENABLED_FLAG"Enabled"
, FV.SUMMARY_FLAG"Parent?"
, NH.LAST_UPDATE_DATE
, FV.HIERARCHY_LEVEL"Level"
FROM FND_FLEX_VALUE_NORM_HIERARCHY NH, FND_FLEX_VALUE_SETS FVS, FND_FLEX_VALUES_TL FVT, FND_FLEX_VALUES FV
WHERE FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
AND NH.PARENT_FLEX_VALUE(+) = FVT.FLEX_VALUE_MEANING
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_NAME LIKE '%ABC%' --- Chart of accounts segment name
-- AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN ('HB')
AND FV.SUMMARY_FLAG = 'Y'
AND FV.FLEX_VALUE LIKE '%XYZ%' --- This is the parent segment values
-- AND NH.PARENT_FLEX_VALUE = '%%'
-- AND FV.ENABLED_FLAG = 'Y'
-- AND FV.HIERARCHY_LEVEL = '2'
ORDER BY 1,3