Segment Value Listing

GL : Segment Value Listing:-
--------------------------------

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.

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"
, 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