Thursday, November 17, 2011

GL : CChart of Account Segment Hierarchy Ranges

/* GL : CChart of Account Segment Hierarchy Ranges
Chart of account segment hierarchy ranges and attributes for parent accounts
*/
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 '%ACCOUNT%' --- Chart of accounts segment name
-- AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN ('BE','LU','ES')
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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect