SEGMENT VALUE SET LISTINGS

/* SEGMENT VALUE SET LISTINGS
Lists single or multiple segment value sets. This is used to perform a QA on chart of accounts values.
Examples of optional where clauses have also been provided below. 
( Tested on Vision 11.5.10.2  June 2007 )*/
SELECT FFVS1.FLEX_VALUE_SET_NAME
--,   FFVS1.FLEX_VALUE_SET_ID
,   FFVAL1.FLEX_VALUE"VALUE"
,     FFVAL1.SUMMARY_FLAG"PARENT ACC ?"
,   FFVTL1.DESCRIPTION
,   FFVAL1.ENABLED_FLAG
,     FH.HIERARCHY_CODE
,   SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),1,1)"BUDGET"
,   SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),3,1)"POST"
,   SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),5,1)"TYPE"
,   SUBSTR(TO_CHAR(FFVAL1.COMPILED_Value_attributes),7,1)"Cntl"
,   SUBSTR(TO_CHAR(ffval1.compiled_value_attributes),9,1)"Recon"
--select distinct ffvs1.FLEX_VALUE_SET_NAME
, ffval1.LAST_UPDATED_BY
, ffval1.LAST_UPDATE_DATE
FROM FND_FLEX_VALUES ffval1
, FND_FLEX_VALUES_TL ffvtl1
, FND_FLEX_VALUE_SETS ffvs1
, FND_ID_FLEX_SEGMENTS seg 
, FND_FLEX_HIERARCHIES_VL fh
WHERE ffval1.FLEX_VALUE_SET_ID(+) = ffvs1.FLEX_VALUE_SET_ID
AND seg.FLEX_VALUE_SET_ID = ffvs1.FLEX_VALUE_SET_ID
AND seg.ID_FLEX_NUM = 51974 /* CoA ID is needed if segment is chart in multple CoA.  Update for you configuration or remove if not applicable. */
AND ffval1.FLEX_VALUE_ID = ffvtl1.FLEX_VALUE_ID(+)
AND ffvs1.FLEX_VALUE_SET_NAME = 'Operations Account'
AND FFVAL1.STRUCTURED_HIERARCHY_LEVEL = FH.HIERARCHY_ID(+)
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) != 'N' -- NON-CONTROL ACCOUNTS ONLY
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) = 'Y' -- CONTROL ACCOUNTS ONLY
--AND FFVAL1.SUMMARY_FLAG = 'Y'
--AND FFVAL1.FLEX_VALUE >= '8000'
--AND FFVAL1.FLEX_VALUE <= '99999'
--AND FFVTL1.DESCRIPTION LIKE '%FTE%'
--AND FFVAL1.FLEX_VALUE LIKE '16%'
ORDER BY FFVS1.FLEX_VALUE_SET_NAME, FFVAL1.FLEX_VALUE