0 Replies Latest reply on May 3, 2020 3:50 PM by Andy Haack

    GL account description value set

    3803247

      Hi,

       

      I developed a report and I used the below query as a select statement for retrieving account and cost center description.

      But the same I need to pass a input run time parameters in application.

      But I couldn't able to register this kind of statement in value set.

       

      Please check and help me out to get the account segment 3 and cost center description segment 4 in the value set

      1. SELECT gl_flexfields_pkg.get_concat_description 
      2.                                            (chart_of_accounts_id, 
      3.                                             code_combination_id 
      4.                                            ) 
      5.              FROM gl_code_combinations
        • 1. Re: GL account description value set
          EdStevens

          3803247 wrote:

           

          Hi,

           

          I developed a report and I used the below query as a select statement for retrieving account and cost center description.

          But the same I need to pass a input run time parameters in application.

          But I couldn't able to register this kind of statement in value set.

           

          Please check and help me out to get the account segment 3 and cost center description segment 4 in the value set

          1. SELECT gl_flexfields_pkg.get_concat_description
          2. (chart_of_accounts_id,
          3. code_combination_id
          4. )
          5. FROM gl_code_combinations

          We can't tell you how to pass a run-time parameter "in application" if we don't know what application are you using to actually execute the query?  No one is looking over your should, so we'd have to guess what you mean by "register this kind of statement in value set."

          • 2. Re: GL account description value set
            Andy Haack

            You need to find the flex values used by the Accounting Flexfield segment. The GL reports in our Blitz Report library use the following SQL for such GL segment LOVs:

             

            select

            ffvv.flex_value value,

            ffvv.description

            from

            fnd_id_flex_segments_vl fifsgv,

            fnd_flex_values_vl ffvv

            where

            gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value,null,fifsgv.id_flex_num)='TRUE' and

            fifsgv.application_id=101 and

            fifsgv.id_flex_code='GL#' and

            fifsgv.application_column_name='SEGMENT3' and

            fifsgv.id_flex_num=:chart_of_accounts_id and

            fifsgv.flex_value_set_id=ffvv.flex_value_set_id and

            sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active+1,sysdate)

            order by

            ffvv.flex_value

             

            If you do not want to exclude the secured values, you can comment the call to gl_security_pkg.validate_segval()

            To translate individual segment values to the description, you can use function segment_description() from the xxen_util package like this:

             

            select

            gcc.segment3 account,

            xxen_util.segment_description(gcc.segment3, 'SEGMENT3', gcc.chart_of_accounts_id) account_description,

            gcc.segment4 cost_center,

            xxen_util.segment_description(gcc.segment4, 'SEGMENT4', gcc.chart_of_accounts_id) cost_center_description

            from

            gl_code_combinations gcc

             

            Or, if you can not easily deploy the xxen_util package and are on DB 12c or later, take the function definition from the package body and use it in a with-clause instead:

             

            with

            function segment_description(

            p_segment_value in varchar2,

            p_column_name in varchar2,

            p_chart_of_accounts_id in number,

            p_application_id in number,

            p_id_flex_code in varchar2

            ) return varchar2 result_cache is

            l_segment_description varchar2(4000);

            begin

              for c in (

              select

              ffvt.description

              from

              fnd_id_flex_segments fifs,

              fnd_flex_values ffv,

              fnd_flex_values_tl ffvt

              where

              fifs.application_column_name=p_column_name and

              fifs.id_flex_num=p_chart_of_accounts_id and

              fifs.application_id=p_application_id and

              fifs.id_flex_code=p_id_flex_code and

              fifs.flex_value_set_id=ffv.flex_value_set_id and

              ffv.parent_flex_value_low is null and

              ffv.flex_value=p_segment_value and

              ffv.flex_value_id=ffvt.flex_value_id and

              ffvt.language=userenv('lang')

              ) loop

                l_segment_description:=c.description;

              end loop;

              return l_segment_description;

            end segment_description;

            select

            gcc.segment3 account,

            xxen_util.segment_description(gcc.segment3, 'SEGMENT3', gcc.chart_of_accounts_id) account_description,

            gcc.segment4 cost_center,

            xxen_util.segment_description(gcc.segment4, 'SEGMENT4', gcc.chart_of_accounts_id) cost_center_description

            from

            gl_code_combinations gcc