Oracle GL account Code Query 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'))
              ;

Oracle Gl Balancing Segment Query based on Organization Profile

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                     
            ;   

Oracle GL segment 1-8 LOV Query

-----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   ;