----------
1) Below given query can be used TO get concatenated Gl code combinations segment
AND its description USING API GL_FLEXFIELDS_PKG.get_concat_description
----------
SELECT
gcc.CODE_COMBINATION_ID,
gcc.CONCATENATED_SEGMENTS,
APPS.GL_FLEXFIELDS_PKG.get_concat_description(gcc.chart_of_accounts_id,
gcc.code_combination_id) acc_description
FROM
gl_code_combinations_kfv gcc;
----------
2)Below given Query can be used
WHEN one dont have access TO GL_FLEXFIELDS_PKG.get_concat_description API.
----------
SELECT
gcc.CODE_COMBINATION_ID,
gcc.CONCATENATED_SEGMENTS,
acc_desc.acc_description
FROM
(
SELECT
gcc.code_combination_id,
A1.DESCRIPTION
||'.'
||A2.DESCRIPTION
||'.'
|| A3.DESCRIPTION
||'.'
|| A4.DESCRIPTION
||'.'
|| A5.DESCRIPTION
||'.'
|| A6.DESCRIPTION
||'.'
|| A7.DESCRIPTION
||'.'
|| A8.DESCRIPTION acc_description
FROM
apps.fnd_flex_values_vl A1,
apps.fnd_flex_values_vl A2,
apps.fnd_flex_values_vl A3,
apps.fnd_flex_values_vl A4,
apps.fnd_flex_values_vl A5,
apps.fnd_flex_values_vl A6,
apps.fnd_flex_values_vl A7,
apps.fnd_flex_values_vl A8,
apps.gl_code_combinations gcc
WHERE
a1.flex_value =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT1'
)
AND a2.flex_value =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT2'
)
AND a3.flex_value =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT3'
)
AND a4.flex_value =gcc.segment4
AND a4.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT4'
)
AND a5.flex_value =gcc.segment5
AND a5.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT5'
)
AND a6.flex_value =gcc.segment6
AND a6.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT6'
)
AND a7.flex_value =gcc.segment7
AND a7.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT7'
)
AND a8.flex_value =gcc.segment8
AND a8.FLEX_VALUE_SET_ID IN
(
SELECT
FLEX_VALUE_SET_ID
FROM
apps.fnd_id_flex_segments
WHERE
application_id = 101
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name='SEGMENT8'
)
)
acc_desc,
gl_code_combinations_kfv gcc
WHERE
gcc.code_combination_id=acc_desc.code_combination_id;
----------
3) Below given query can be used TO get EACH segment description seperately
USING API gl_flexfields_pkg.get_description_sql
---------
SELECT
gcc.segment1,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
1, ----- Position of segment
gcc.segment1 ---- Segment value
) Segment1_desc,
gcc.segment2,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
2, ----- Position of segment
gcc.segment2 ---- Segment value
) Segment2_desc,
gcc.segment3,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
3, ----- Position of segment
gcc.segment3 ---- Segment value
) Segment3_desc,
gcc.segment4,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
4, ----- Position of segment
gcc.segment4 ---- Segment value
) Segment4_desc,
gcc.segment5,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
5, ----- Position of segment
gcc.segment5 ---- Segment value
) Segment5_desc,
gcc.segment6,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
6, ----- Position of segment
gcc.segment6 ---- Segment value
) Segment6_desc,
gcc.segment7,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
7, ----- Position of segment
gcc.segment7 ---- Segment value
) Segment7_desc,
gcc.segment8,
gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
8, ----- Position of segment
gcc.segment8 ---- Segment value
) Segment8_desc
FROM
gl_code_combinations gcc;
Comments
Post a Comment