Chart of Accounts Structure Overview

GL : Chart of Accounts Structure Overview:-
--------------------------------------------------

CHART OF ACCOUNTS STRUCTURE

Gives an overview of the chart of accounts definitions and also status.
This is used when implementing multiple charts of accounts to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries.

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

SELECT FST.ID_FLEX_STRUCTURE_NAME
--, FST.DESCRIPTION
--, FST.ID_FLEX_NUM
--, FST.ID_FLEX_STRUCTURE_CODE
, FST.CROSS_SEGMENT_VALIDATION_FLAG"X-VAL"
, FST.FREEZE_STRUCTURED_HIER_FLAG"FZ-HIER"
, FST.FREEZE_FLEX_DEFINITION_FLAG"FZ-DEFN"
, FSEG.SEGMENT_NUM "SEG#"
, FSEG.SEGMENT_NAME "SEG NAME"
, VS.FLEX_VALUE_SET_NAME "VALUE SET"
, FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
, FSEG.DEFAULT_TYPE"DEF TYPE"
, FSEG.DEFAULT_VALUE"DEF. VALUE"
, FSEG.ENABLED_FLAG"ENBLD"
, FSEG.REQUIRED_FLAG"REQD"
FROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('HB')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM

Read More

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

Read More