ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger 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$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 D7A1F83E5C9B4D21E0530100007FD1A6 with gcck as (select &materialize_hint gcck.* from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) and 2=2 ) select x.* from ( -- Check 1: All GL Balances rows for the account/period (unfiltered) select '1 - GL Balances' check_type, 'All gl_balances rows for this account/period/ledger' check_description, gcck.code_combination_id||'' detail1_ccid, gb.currency_code detail2_currency, nvl(gb.translated_flag,'(null)') detail3_translated_flag, nvl(gb.template_id||'','(null)') detail4_template_id, to_char(gb.period_net_dr) detail5_period_net_dr, to_char(gb.period_net_cr) detail6_period_net_cr, to_char(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)) detail7_period_net, to_char(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) detail8_begin_balance, to_char(nvl(gb.begin_balance_dr,0)+nvl(gb.period_net_dr,0)-nvl(gb.begin_balance_cr,0)-nvl(gb.period_net_cr,0)) detail9_end_balance, gcck.concatenated_segments detail10_account from gl_balances gb, gcck, gl_ledgers gl where gl.name=:ledger and gb.ledger_id=gl.ledger_id and gb.period_name=:period_name and gb.actual_flag='A' and gb.code_combination_id=gcck.code_combination_id union all -- Check 2: Balances matching FSG drilldown filter (translated_flag is null, template_id is null, non-zero amounts) select '2 - Drilldown Filter' check_type, 'Balances matching drilldown filter (translated_flag is null, template_id is null, summary_flag=N, non-zero)' check_description, gcck.code_combination_id||'' detail1_ccid, gb.currency_code detail2_currency, '(null)' detail3_translated_flag, '(null)' detail4_template_id, to_char(gb.period_net_dr) detail5_period_net_dr, to_char(gb.period_net_cr) detail6_period_net_cr, to_char(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)) detail7_period_net, to_char(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) detail8_begin_balance, to_char(nvl(gb.begin_balance_dr,0)+nvl(gb.period_net_dr,0)-nvl(gb.begin_balance_cr,0)-nvl(gb.period_net_cr,0)) detail9_end_balance, gcck.concatenated_segments detail10_account from gl_balances gb, gcck, gl_ledgers gl where gl.name=:ledger and gb.ledger_id=gl.ledger_id and gb.period_name=:period_name and gb.actual_flag='A' and gb.translated_flag is null and gb.template_id is null and gb.code_combination_id=gcck.code_combination_id and gcck.summary_flag='N' and (nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)<>0 or nvl(gb.period_net_dr,0)<>0 or nvl(gb.period_net_cr,0)<>0) union all -- Check 3: Posted journal line count and totals select '3 - Posted Journals' check_type, 'Count and sum of posted journal lines (gjh.status=P) for this account/period' check_description, to_char(count(*)) detail1_ccid, null detail2_currency, null detail3_translated_flag, null detail4_template_id, to_char(sum(nvl(gjl.accounted_dr,0))) detail5_period_net_dr, to_char(sum(nvl(gjl.accounted_cr,0))) detail6_period_net_cr, to_char(sum(nvl(gjl.accounted_dr,0))-sum(nvl(gjl.accounted_cr,0))) detail7_period_net, null detail8_begin_balance, null detail9_end_balance, null detail10_account from gl_je_headers gjh, gl_je_lines gjl, gcck, gl_ledgers gl where gl.name=:ledger and gjh.ledger_id=gl.ledger_id and gjh.period_name=:period_name and gjh.status='P' and gjh.actual_flag='A' and gjh.je_header_id=gjl.je_header_id and gjl.code_combination_id=gcck.code_combination_id union all -- Check 4: Unposted journal line count and totals select '4 - Unposted Journals' check_type, 'Count and sum of unposted journal lines (gjh.status=U) for this account/period' check_description, to_char(count(*)) detail1_ccid, null detail2_currency, null detail3_translated_flag, null detail4_template_id, to_char(sum(nvl(gjl.accounted_dr,0))) detail5_period_net_dr, to_char(sum(nvl(gjl.accounted_cr,0))) detail6_period_net_cr, to_char(sum(nvl(gjl.accounted_dr,0))-sum(nvl(gjl.accounted_cr,0))) detail7_period_net, null detail8_begin_balance, null detail9_end_balance, null detail10_account from gl_je_headers gjh, gl_je_lines gjl, gcck, gl_ledgers gl where gl.name=:ledger and gjh.ledger_id=gl.ledger_id and gjh.period_name=:period_name and gjh.status='U' and gjh.actual_flag='A' and gjh.je_header_id=gjl.je_header_id and gjl.code_combination_id=gcck.code_combination_id union all -- Check 5: GL period status select '5 - Period Status' check_type, 'GL period status for the selected ledger and period' check_description, gps.closing_status detail1_ccid, decode(gps.closing_status,'O','Open','C','Closed','F','Future Entry','N','Never Opened','P','Permanently Closed',gps.closing_status) detail2_currency, to_char(gp.start_date,'DD-MON-YYYY') detail3_translated_flag, to_char(gp.end_date,'DD-MON-YYYY') detail4_template_id, null detail5_period_net_dr, null detail6_period_net_cr, null detail7_period_net, null detail8_begin_balance, null detail9_end_balance, null detail10_account from gl_period_statuses gps, gl_periods gp, gl_ledgers gl where gl.name=:ledger and gps.ledger_id=gl.ledger_id and gps.application_id=101 and gps.period_name=:period_name and gp.period_set_name=gl.period_set_name and gp.period_name=gps.period_name and gp.period_type=gl.accounted_period_type union all -- Check 6: Account properties (summary_flag, enabled_flag, detail_posting, detail_budgeting) select '6 - Account Properties' check_type, 'Value 1=CCID, Value 2=summary_flag (must be N), Value 3=enabled_flag, Value 4=detail_posting, Value 5=detail_budgeting, Value 6=start_date, Value 7=end_date' check_description, gcc.code_combination_id||'' detail1_ccid, gcc.summary_flag detail2_currency, gcc.enabled_flag detail3_translated_flag, gcc.detail_posting_allowed_flag detail4_template_id, gcc.detail_budgeting_allowed_flag detail5_period_net_dr, to_char(gcc.start_date_active,'DD-MON-YYYY') detail6_period_net_cr, to_char(gcc.end_date_active,'DD-MON-YYYY') detail7_period_net, null detail8_begin_balance, null detail9_end_balance, gcck.concatenated_segments detail10_account from gcck, gl_code_combinations gcc where gcck.code_combination_id=gcc.code_combination_id union all -- Check 7: Flex value security check per code combination select '7 - Security Access' check_type, 'Value 1=CCID, Value 2=validate_access result (must be TRUE), Value 3=has_flex_value_security (Y/N)' check_description, gcck.code_combination_id||'' detail1_ccid, gl_security_pkg.validate_access(null,gcck.code_combination_id) detail2_currency, xxen_util.has_flex_value_security detail3_translated_flag, null detail4_template_id, null detail5_period_net_dr, null detail6_period_net_cr, null detail7_period_net, null detail8_begin_balance, null detail9_end_balance, gcck.concatenated_segments detail10_account from gcck union all -- Check 8: GL Balances for the previous period (comparison baseline) select '8 - Previous Period' check_type, 'GL Balances for previous period (comparison with working period)' check_description, gcck.code_combination_id||'' detail1_ccid, gb.currency_code detail2_currency, nvl(gb.translated_flag,'(null)') detail3_translated_flag, nvl(gb.template_id||'','(null)') detail4_template_id, to_char(gb.period_net_dr) detail5_period_net_dr, to_char(gb.period_net_cr) detail6_period_net_cr, to_char(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0)) detail7_period_net, to_char(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) detail8_begin_balance, to_char(nvl(gb.begin_balance_dr,0)+nvl(gb.period_net_dr,0)-nvl(gb.begin_balance_cr,0)-nvl(gb.period_net_cr,0)) detail9_end_balance, gcck.concatenated_segments detail10_account from gl_balances gb, gcck, gl_ledgers gl where gl.name=:ledger and gb.ledger_id=gl.ledger_id and gb.period_name=(select gp2.period_name from gl_periods gp2, gl_periods gp1 where gp1.period_set_name=gp2.period_set_name and gp1.period_type=gp2.period_type and gp1.period_name=:period_name and gp1.period_set_name=gl.period_set_name and gp1.period_type=gl.accounted_period_type and gp2.period_year*100+gp2.period_num=(select max(gp3.period_year*100+gp3.period_num) from gl_periods gp3 where gp3.period_set_name=gp1.period_set_name and gp3.period_type=gp1.period_type and gp3.period_year*100+gp3.period_num<gp1.period_year*100+gp1.period_num)) and gb.actual_flag='A' and gb.code_combination_id=gcck.code_combination_id ) x order by x.check_type, x.detail10_account nulls last Y US FSG Drilldown Diagnostic Troubleshooting report for FSG journal drilldown issues. Checks gl_balances, posted/unposted journals, period status, account properties, and flex value security for a given account and period. Use the GL segment parameters to filter to the specific account combination that is not drilling down. Enginatics &materialize_hint 2=2 :ledger :period_name 1 10 :ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 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$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name Y US Ledger 2 20 :period_name LOV custom Y select gp.period_name value, gp.start_date description from gl_periods gp, gl_ledgers gl where gl.name=:$flex$.Ledger and gp.period_set_name=gl.period_set_name and gp.period_type=gl.accounted_period_type order by gp.start_date desc Y US Period 3 100 2=2 gcck.segment1=:segment1 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT1' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT1 4 &materialize_hint /*+ materialize*/ US GL_SEGMENT1 5 110 2=2 gcck.segment2=:segment2 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT2' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT2 6 &materialize_hint /*+ materialize*/ US GL_SEGMENT2 7 120 2=2 gcck.segment3=:segment3 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT3' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT3 8 &materialize_hint /*+ materialize*/ US GL_SEGMENT3 9 130 2=2 gcck.segment4=:segment4 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT4' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT4 10 &materialize_hint /*+ materialize*/ US GL_SEGMENT4 11 140 2=2 gcck.segment5=:segment5 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT5' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT5 12 &materialize_hint /*+ materialize*/ US GL_SEGMENT5 13 150 2=2 gcck.segment6=:segment6 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT6' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT6 14 &materialize_hint /*+ materialize*/ US GL_SEGMENT6 15 160 2=2 gcck.segment7=:segment7 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT7' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT7 16 &materialize_hint /*+ materialize*/ US GL_SEGMENT7 17 170 2=2 gcck.segment8=:segment8 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT8' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT8 18 &materialize_hint /*+ materialize*/ US GL_SEGMENT8 19 180 2=2 gcck.segment9=:segment9 LOV custom Y select ffvv.flex_value value, ffvv.description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.enabled_flag='Y' and fifsgv.application_column_name='SEGMENT9' and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.Ledger) order by ffvv.flex_value US GL_SEGMENT9 20 &materialize_hint /*+ materialize*/ US GL_SEGMENT9 :$flex$.Ledger Ledger GL_SEGMENT1 :$flex$.Ledger Ledger GL_SEGMENT2 :$flex$.Ledger Ledger GL_SEGMENT3 :$flex$.Ledger Ledger GL_SEGMENT4 :$flex$.Ledger Ledger GL_SEGMENT5 :$flex$.Ledger Ledger GL_SEGMENT6 :$flex$.Ledger Ledger GL_SEGMENT7 :$flex$.Ledger Ledger GL_SEGMENT8 :$flex$.Ledger Ledger GL_SEGMENT9 :$flex$.Ledger Ledger Period