Home » Applications » Oracle Fusion Apps & E-Business Suite » link code for FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS tables
link code for FND_FLEX_VALUES_VL and GL_CODE_COMBINATIONS tables [message #344306] Fri, 29 August 2008 06:19 Go to next message
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 select
SELECT 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 select
SELECT 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 name
SELECT 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 Go to previous message
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
Previous Topic: HRMS Issue
Next Topic: Concurrent Program Parameter
Goto Forum:
  


Current Time: Sat Dec 03 16:22:02 CST 2016

Total time taken to generate the page: 0.08660 seconds