ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDED379D2E0530100007F1FF2 GL Account Type Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='ACCOUNT_TYPE' and flvv.view_application_id=0 and flvv.security_group_id=0 order by decode(flvv.lookup_code,'A',1,'L',2,'O',3,'R',4,'E',5,'C',6,'D',7) US A95C471001B20DD1E0530100007FBF83 GL Ledger (all) Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and gl.object_type_code='L' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name D Alle Hauptbücher E Todos los libros de contabilidad F Tous les livres I Tutti i libri contabili US All ledgers 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 05D91B087666CE00E0630100007F16EF select gcck.chart_of_accounts, xxen_util.yes(gcck.enabled_flag) enabled, xxen_util.yes(gcck.preserve_flag) preserved, gcck.concatenated_segments, nvl(xxen_util.meaning(gcck.gl_account_type,'ACCOUNT_TYPE',0),gcck.gl_account_type) account_type, (select gst.template_name from gl_summary_templates gst where gcck.template_id=gst.template_id) summary_template, &balance_columns gcck.start_date_active date_from, gcck.end_date_active date_to, xxen_util.yes(gcck.detail_posting_allowed) posting_allowed, xxen_util.yes(gcck.detail_budgeting_allowed) budgeting_allowed, (select gcck2.concatenated_segments from gl_code_combinations_kfv gcck2 where gcck.alternate_code_combination_id=gcck2.code_combination_id) alternate_account, xxen_util.yes(gcck.reconciliation_flag) reconcile, xxen_util.yes(gcck.igi_balanced_budget_flag) enforce_balanced_budget, &segment_columns &dff_columns xxen_util.meaning(gcck.flex_val_account_type,'ACCOUNT_TYPE',0) flex_value_account_type, xxen_util.user_name(gcck.last_updated_by) last_updated_by, xxen_util.client_time(gcck.last_update_date) last_update_date, gcck.code_combination_id from ( select gcck.*, substr((select ffv.compiled_value_attributes from fnd_flex_values ffv where gcck.flex_value_set_id=ffv.flex_value_set_id and gcck.account_segment_value=ffv.flex_value and ffv.parent_flex_value_low is null),5,1) flex_val_account_type from ( select decode(gcck.account_segment, 'SEGMENT1',gcck.segment1, 'SEGMENT2',gcck.segment2, 'SEGMENT3',gcck.segment3, 'SEGMENT4',gcck.segment4, 'SEGMENT5',gcck.segment5, 'SEGMENT6',gcck.segment6, 'SEGMENT7',gcck.segment7, 'SEGMENT8',gcck.segment8, 'SEGMENT9',gcck.segment9, 'SEGMENT10',gcck.segment10, 'SEGMENT11',gcck.segment11, 'SEGMENT12',gcck.segment12, 'SEGMENT13',gcck.segment13 ) account_segment_value, fifs.flex_value_set_id, gcck.* from ( select fifsv.id_flex_structure_name chart_of_accounts, ( select fsav.application_column_name from fnd_segment_attribute_values fsav where gcck.chart_of_accounts_id=fsav.id_flex_num and fsav.application_id=101 and fsav.id_flex_code='GL#' and fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y' ) account_segment, gcck.* from gl_code_combinations_kfv gcck, fnd_id_flex_structures_vl fifsv where 1=1 and gcck.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 ) gcck, fnd_id_flex_segments fifs where gcck.chart_of_accounts_id=fifs.id_flex_num(+) and gcck.account_segment=fifs.application_column_name(+) and fifs.application_id(+)=101 and fifs.id_flex_code(+)='GL#' ) gcck ) gcck, ( select distinct gb.code_combination_id, gl.name ledger, gb.currency_code, max(gb.end_balance) keep (dense_rank last order by gp.period_year, gp.period_num) over (partition by gb.code_combination_id, gl.ledger_id, gb.currency_code) end_balance, max(gb.period_name) keep (dense_rank last order by gp.period_year, gp.period_num) over (partition by gb.code_combination_id, gl.ledger_id, gb.currency_code) period_name, max(gp.end_date) keep (dense_rank last order by gp.period_year, gp.period_num) over (partition by gb.code_combination_id, gl.ledger_id, gb.currency_code) end_date, gb.ledger_id from (select nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)+nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0) end_balance, gb.* from gl_balances gb) gb, gl_ledgers gl, gl_periods gp where 2=2 and '&show_balances'='Y' and gb.ledger_id=gl.ledger_id and gl.period_set_name=gp.period_set_name and gb.period_name=gp.period_name and gb.actual_flag='A' and nvl(gb.translated_flag,'x')!='R' ) gb, ( select distinct gjl.ledger_id, gjl.code_combination_id, gjh.currency_code, max(gjl.effective_date) over (partition by gjl.ledger_id, gjl.code_combination_id, gjh.currency_code) last_posted_date from gl_je_headers gjh, gl_je_lines gjl where gjh.je_header_id=gjl.je_header_id and gjl.status='P' ) gjl where 3=3 and gcck.code_combination_id=gb.code_combination_id(+) and gb.ledger_id=gjl.ledger_id(+) and gb.code_combination_id=gjl.code_combination_id(+) and gb.currency_code=gjl.currency_code(+) order by gcck.chart_of_accounts, summary_template, gcck.concatenated_segments &order_by Y US GL Code Combinations Parameter 'Show Misclassified Accounts' can be used to identify code combinations, which have a different account type than their flexfield value setup. There is an Oracle note explaining the implications of correcting these: R12: Troubleshooting Misclassified Accounts in General Ledger (KB706390) https://support.oracle.com/support/?kmContentId=872162 Enginatics &balance_columns &dff_columns &order_by &segment_columns &show_balances 1=1 2=2 3=3 1 10 1=1 gcck.chart_of_accounts_id=:chart_of_accounts_id LOV custom Y select fifsv.id_flex_num id, fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name US Chart of Accounts 2 &segment_columns select distinct 'gcck.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)|| 'xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'|| ( select ', gcck.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||')'||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length-5)||' desc",' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( :ledger is null and :chart_of_accounts_id is null or (:ledger is null and xxen_util.contains(:chart_of_accounts_id,fifsv.id_flex_num)='Y') or fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') ) order by min_id_flex_num, min_segment_num US Chart of Accounts 3 20 1=1 gcck.chart_of_accounts_id in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) LOV GL Ledger (all) A95C471001B20DD1E0530100007FBF83 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and gl.object_type_code='L' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name US Ledger 4 &segment_columns select distinct 'gcck.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",'||chr(10)|| 'xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id'|| ( select ', gcck.'||lower(fifs.application_column_name) from fnd_id_flex_segments fifs where fifsv.application_id=fifs.application_id and fifsv.id_flex_code=fifs.id_flex_code and fifsv.id_flex_num=fifs.id_flex_num and fifsv.enabled_flag=fifs.enabled_flag and fifs.flex_value_set_id=(select ffvs.parent_flex_value_set_id from fnd_flex_value_sets ffvs where fifsv.flex_value_set_id=ffvs.flex_value_set_id) )||')'||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length-5)||' desc",' text, min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num, min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( :ledger is null and :chart_of_accounts_id is null or (:ledger is null and xxen_util.contains(:chart_of_accounts_id,fifsv.id_flex_num)='Y') or fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:ledger,gl.name)='Y') ) order by min_id_flex_num, min_segment_num US Ledger 5 2=2 gl.name=:ledger US Ledger 6 30 &show_balances <parameter_value> LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Balance and Posted Date 7 &balance_columns gb.ledger, gb.currency_code, gb.end_balance, gb.period_name, gb.end_date, gjl.last_posted_date, US Show Balance and Posted Date 8 &order_by ,gb.ledger, gb.currency_code US Show Balance and Posted Date 9 40 1=1 gcck.gl_account_type=xxen_util.lookup_code(:account_type,'ACCOUNT_TYPE',0) LOV GL Account Type 8E2FF36EDED379D2E0530100007F1FF2 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='ACCOUNT_TYPE' and flvv.view_application_id=0 and flvv.security_group_id=0 order by decode(flvv.lookup_code,'A',1,'L',2,'O',3,'R',4,'E',5,'C',6,'D',7) US Account Type 10 50 3=3 gcck.gl_account_type<>gcck.flex_val_account_type and gcck.account_segment_value<>'T' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Misclassified Accounts 11 60 1=1 gcck.summary_flag='N' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Child Accounts Only 12 70 1=1 gcck.segment1 like :segment1_like Char US Segment1 like 13 80 1=1 gcck.segment1>=:segment1_from Char US Segment1 From 14 90 1=1 gcck.segment1<=:segment1_to Char :$flex$.segment1_from US Segment1 To 15 100 1=1 gcck.segment2 like :segment2_like Char US Segment2 like 16 110 1=1 gcck.segment2>=:segment2_from Char US Segment2 From 17 120 1=1 gcck.segment2<=:segment2_to Char :$flex$.segment2_from US Segment2 To 18 130 1=1 gcck.segment3 like :segment3_like Char US Segment3 like 19 140 1=1 gcck.segment3>=:segment3_from Char US Segment3 From 20 150 1=1 gcck.segment3<=:segment3_to Char :$flex$.segment3_from US Segment3 To 21 160 1=1 gcck.segment4 like :segment4_like Char US Segment4 like 22 170 1=1 gcck.segment4>=:segment4_from Char US Segment4 From 23 180 1=1 gcck.segment4<=:segment4_to Char :$flex$.segment4_from US Segment4 To 24 190 1=1 gcck.segment5 like :segment5_like Char US Segment5 like 25 200 1=1 gcck.segment5>=:segment5_from Char US Segment5 From 26 210 1=1 gcck.segment5<=:segment5_to Char :$flex$.segment5_from US Segment5 To :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.segment1_from Segment1 From Segment1 To :$flex$.segment2_from Segment2 From Segment2 To :$flex$.segment3_from Segment3 From Segment3 To :$flex$.segment4_from Segment4 From Segment4 To :$flex$.segment5_from Segment5 From Segment5 To