link code for FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS tables [message #344306] |
Fri, 29 August 2008 06:19  |
ramisetti1
Messages: 30 Registered: January 2007
|
Member |
|
|
hi I am developing one report for that I need to capture the values from FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS
(segments list and theire meaning(description)
and balace amount also
please provide the link between these tables
from where we can get balacing amount with respect to above segments
suresh
|
|
|
Re: link code for FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS tables [message #344456 is a reply to message #344306] |
Fri, 29 August 2008 20:17   |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Very complex concept!!
FND_FLEX_VALUES_VL --> contains the values for the value sets (can be independent, Dependent, Table)
GL_CODE_COMBINATIONS --> contains the code_combination_ids, which is a set of segments. Segments will use the values of the above value sets.
Lets say if you want to find out the Balancing Segment:
You can get the segment name (segment1,segment2,..., or segmentn) using the following selectSELECT application_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING'; For the segment name, you can know which value set is attached using the following selectSELECT flex_value_set_id
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND application_column_name = p_segment_name;
If the value set in Independent, FND_FLEX_VALUES_VL.FLEX_VALUE contains the value of the segment name with parent_flex_value_low as null.
If the value set is Dependent, FND_FLEX_VALUES_VL.FLEX_VALUE contains the value of the segment name with parent_flex_value_low as the value as parent segment nameSELECT application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND application_id = 101
AND flex_value_set_id =
(SELECT parent_flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id); I don't think you can use direct joins. You may need to use lexicals to achieve this.
By
Vamsi
|
|
|
Re: link code for FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS tables [message #344520 is a reply to message #344456] |
Sat, 30 August 2008 03:20  |
ramisetti1
Messages: 30 Registered: January 2007
|
Member |
|
|
Thank you Vamsi,
my actual Requirement is :
All expenses are start with 5 and 6 So for the ease of query writing instead of considering accounts mentioned in attached report we will pull all data as below.
- All Row lines data are accounts Description of accounts start with 5 & 6.
- All Column data are store location codes start with 101 to 999
here segment4 is location and segment6 is natural account
here developed query as below
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT gcc.code_combination_id, a.description locattions,
b.description particulars, (gb.period_net_dr - gb.period_net_cr) balance
FROM (SELECT ffvv.flex_value, ffvv.description
FROM fnd_flex_values_vl ffvv, fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = 'ABRL_GL_Location'
AND ffvs.flex_value_set_id = ffvv.flex_value_set_id) a,
(SELECT ffvv.flex_value, ffvv.description
FROM fnd_flex_values_vl ffvv, fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = 'ABRL_GL_Account'
AND ffvs.flex_value_set_id = ffvv.flex_value_set_id) b,
gl_code_combinations gcc,
gl_balances gb
WHERE (segment6 LIKE '5%' OR segment6 LIKE '6%')
AND SUBSTR (segment4, 1, 3) BETWEEN (SELECT MIN (SUBSTR (segment4, 1, 3))
FROM gl_code_combinations
WHERE SUBSTR (segment4, 1, 3) >= 101)
AND (SELECT MAX (SUBSTR (segment4, 1, 3))
FROM gl_code_combinations
WHERE SUBSTR (segment4, 1, 3) <= 299)
AND a.flex_value = gcc.segment4
AND b.flex_value = gcc.segment6
AND gb.code_combination_id = gcc.code_combination_id
++++++++++++++++++++++++++++++++++++++++++++++++++++++
here I am confusing from which table I need to capture bancing amount for particular account and location
just I used GL_balances ,but it is giving duplicate records because it showing period wise
here I am attaching required out put format in exel sheet
please check this
|
|
|