-----Segment1- Value Set For GL Account-----
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt,
gl_ledgers gl,
hr_operating_units hou,
gl_code_combinations_kfv gcck
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.flex_value=gcck.segment1
AND gl.ledger_id = hou.set_of_books_id
AND gl.RET_EARN_CODE_COMBINATION_ID =
gcck.CODE_COMBINATION_ID
and hou.organization_id=fnd_profile.VALUE('ORG_ID')
Order by ffv.flex_value
;
--------segment2-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Major/Minor Program'
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;
--------segment3-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch/Dep/Section/Unit' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;
--------segment4-----Dynamic based on Security Profile------
SELECT c.flex_value, ffvt.DESCRIPTION
FROM fnd_flex_value_rule_usages a,
fnd_flex_value_rule_lines b,
fnd_flex_values c,
fnd_flex_value_sets ffvs,
fnd_flex_values_tl ffvt
WHERE a.flex_value_rule_id = b.flex_value_rule_id
AND a.flex_value_set_id = b.flex_value_set_id
AND b.flex_value_set_id = c.flex_value_set_id
AND c.flex_value BETWEEN b.flex_value_low AND b.flex_value_high
AND b.include_exclude_indicator = 'I'
AND c.summary_flag <> 'Y'
AND c.ENABLED_FLAG = 'Y'
AND c.END_DATE_ACTIVE IS NULL
AND TO_CHAR (a.flex_value_set_id) =
(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT') --valuelistof(nq_session.gl_sec_account_valuesets____ebs)
AND ffvs.flex_value_set_id = c.flex_value_set_id
AND c.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV ('LANG')
AND flex_value_set_name LIKE 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND TO_CHAR (a.responsibility_id) = fnd_profile.VALUE ('RESP_ID') --'52489' --valuelistof(nq_session.gl_sec_ebs_resp_id)
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID')
UNION
SELECT
ffv.flex_value ,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_ACCOUNT' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
AND ffv.end_date_active is null
AND 0=(select count(1) from fnd_flex_value_rule_usages a,fnd_flex_value_rule_lines b
where
a.FLEX_VALUE_RULE_ID=b.FLEX_VALUE_RULE_ID
and a.flex_value_set_id=b.flex_value_set_id
and a.flex_value_set_id =(select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name LIKE 'AG_ACCOUNT')
AND TO_CHAR (a.responsibility_id) =fnd_profile.VALUE ('RESP_ID')
AND a.application_id = fnd_profile.VALUE ('RESP_APPL_ID'))
;
--------segment5-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Circle/Branch' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;
--------segment6-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 1' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;
--------segment7-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 2' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;
--------segment8-----------
SELECT ffv.flex_value,ffvt.DESCRIPTION
FROM
fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
and flex_value_set_name like 'AG_Future 3' --- Add your Flexfield Valueset name of Company for that segment
AND ffv.ENABLED_FLAG='Y'
AND ffv.SUMMARY_FLAG !='Y'
Order by ffv.flex_value ;