OBIA Configuration SQLs

EBS Configuration SQL

 
1) Determine NLS_LANG
 

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ( 'NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET' )

 
2) Determine up Currency code
 
Select currency_code from gl_set_of_books where consolidation_sob_flag='Y'
 
3) Get SOB  - To Limit ETL to a set of books
 
select set_of_books_id, name
From gl_set_of_books;
 
 
4) Limit $$INitial Extract Date
 

SELECT PERIOD_YEAR,
       PERIOD_NUM,
       to_char(START_DATE, 'YYYYMMDD')
from   GL_PERIOD_STATUSES
where  set_of_books_id = '289'
and    period_year between 2002 and 2009
and    application_id = 101
and    adjustment_period_flag = 'N' order by period_year, period_num;

 
5) Determine Code Combination for a Chart of account and list natural accounts within this COA  
 

This query gives us a list of the code combinations that make up an COA and we need to find the flex_value_set_id for the natural account, enter the sob as a parameter to this query

 

 

Column Name	Segment Name	Natural Account	Flex_Value_Set_Id
Segment1	Organisation
Segment2	Cost Centre
Segment3	Account	Y	1007947
Segment4	Analysis
Segment5	Output
Segment6	Public Services

 

 

SELECT sob.name sob_name,  sob.set_of_books_id sob_id,  sob.chart_of_accounts_id coa_id,  fifst.id_flex_structure_name struct_name,  ifs.segment_name,  ifs.application_column_name column_name,  sav1.attribute_value balancing,  sav2.attribute_value cost_center,  sav3.attribute_value natural_account,  sav4.attribute_value intercompany,  sav5.attribute_value secondary_tracking,  sav6.attribute_value GLOBAL,  ffvs.flex_value_set_name,  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs,  fnd_id_flex_structures_tl fifst,  fnd_segment_attribute_values sav1,  fnd_segment_attribute_values sav2,  fnd_segment_attribute_values sav3,  fnd_segment_attribute_values sav4,  fnd_segment_attribute_values sav5,  fnd_segment_attribute_values sav6,  fnd_id_flex_segments ifs,  fnd_flex_value_sets ffvs,  gl_sets_of_books sob
WHERE 1 = 1
AND fifs.id_flex_code = 'GL#' AND fifs.application_id = fifst.application_id AND fifs.id_flex_code = fifst.id_flex_code AND fifs.id_flex_num = fifst.id_flex_num AND fifs.application_id = ifs.application_id AND fifs.id_flex_code = ifs.id_flex_code AND fifs.id_flex_num = ifs.id_flex_num AND sav1.application_id = ifs.application_id AND sav1.id_flex_code = ifs.id_flex_code AND sav1.id_flex_num = ifs.id_flex_num AND sav1.application_column_name = ifs.application_column_name AND sav2.application_id = ifs.application_id AND sav2.id_flex_code = ifs.id_flex_code AND sav2.id_flex_num = ifs.id_flex_num AND sav2.application_column_name = ifs.application_column_name AND sav3.application_id = ifs.application_id AND sav3.id_flex_code = ifs.id_flex_code AND sav3.id_flex_num = ifs.id_flex_num AND sav3.application_column_name = ifs.application_column_name AND sav4.application_id = ifs.application_id AND sav4.id_flex_code = ifs.id_flex_code AND sav4.id_flex_num = ifs.id_flex_num AND sav4.application_column_name = ifs.application_column_name AND sav5.application_id = ifs.application_id AND sav5.id_flex_code = ifs.id_flex_code AND sav5.id_flex_num = ifs.id_flex_num AND sav5.application_column_name = ifs.application_column_name AND sav6.application_id = ifs.application_id AND sav6.id_flex_code = ifs.id_flex_code AND sav6.id_flex_num = ifs.id_flex_num AND sav6.application_column_name = ifs.application_column_name AND sav1.segment_attribute_type = 'GL_BALANCING' AND sav2.segment_attribute_type = 'FA_COST_CTR' AND sav3.segment_attribute_type = 'GL_ACCOUNT' AND sav4.segment_attribute_type = 'GL_INTERCOMPANY' AND sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING' AND sav6.segment_attribute_type = 'GL_GLOBAL' AND ifs.id_flex_num = sob.chart_of_accounts_id AND ifs.flex_value_set_id = ffvs.flex_value_set_id -- comment the next expression to show all books-- currently it show the info for the site level set profile option value-- and    sob.set_of_books_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)AND sob.name = 'Progress UK'ORDER BY sob.name,  sob.chart_of_accounts_id,  ifs.application_column_name;

 

 

 
This next query returns all of the natual accounts for the flex_value_set
 

SELECT DISTINCT FND_FLEX_VALUES.FLEX_VALUE_SET_ID, FND_FLEX_VALUES.FLEX_VALUE, FND_FLEX_VALUES_TL.DESCRIPTION,substr(FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,7,1) AS Account_Type-- FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,FND_FLEX_VALUES.SUMMARY_FLAG
FROM FND_FLEX_VALUES, FND_FLEX_VALUES_TL, FND_ID_FLEX_SEGMENTS, FND_SEGMENT_ATTRIBUTE_VALUES
WHERE FND_FLEX_VALUES.FLEX_VALUE_ID = FND_FLEX_VALUES_TL.FLEX_VALUE_ID AND FND_FLEX_VALUES_TL.LANGUAGE ='US'AND FND_ID_FLEX_SEGMENTS.FLEX_VALUE_SET_ID =FND_FLEX_VALUES.FLEX_VALUE_SET_ID AND FND_ID_FLEX_SEGMENTS.APPLICATION_ID = 101 AND FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE ='GL#' AND FND_ID_FLEX_SEGMENTS.ID_FLEX_NUM =FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_NUM AND FND_SEGMENT_ATTRIBUTE_VALUES.APPLICATION_ID =101 AND FND_SEGMENT_ATTRIBUTE_VALUES.ID_FLEX_CODE = 'GL#' AND FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME=FND_SEGMENT_ATTRIBUTE_VALUES.APPLICATION_COLUMN_NAME AND FND_SEGMENT_ATTRIBUTE_VALUES.SEGMENT_ATTRIBUTE_TYPE ='GL_ACCOUNT' AND FND_SEGMENT_ATTRIBUTE_VALUES.ATTRIBUTE_VALUE ='Y'and FND_FLEX_VALUES.FLEX_VALUE_SET_ID = '1007947'-- Ignore Summary Accountsand FND_FLEX_VALUES.SUMMARY_FLAG = 'N'-- Only Revenue Accounts-- and substr(FND_FLEX_VALUES.COMPILED_VALUE_ATTRIBUTES,7,1) NOT IN ('A','E','L','O') ;