ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN CA84F9DBA2FAAF43E05362FB0905DD14 with xtd as ( select /*+ materialize */ distinct gps.ledger_id, xtdd.code_combination_id, gps.period_name from xla_tb_defn_details xtdd, xla_tb_definitions_vl xtdv, gl_period_statuses gps where xtdd.definition_code = :p_definition_code and xtdv.definition_code = xtdd.definition_code and xtdd.code_combination_id is not null and gps.application_id =200 and gps.ledger_id = xtdv.ledger_id and gps.start_date >= nvl(:p_from_date,gps.start_date+ 1) and gps.end_date <= nvl(:p_as_of_date,gps.end_date+ 1) and nvl(gps.adjustment_period_flag,'N')='N' union select /*+ materialize */ DISTINCT xtdv.ledger_id, gcck.code_combination_id, gps.period_name from xla_tb_definitions_vl xtdv, xla_tb_def_seg_ranges xsr, gl_code_combinations_kfv gcck, gl_period_statuses gps where xtdv.definition_code = :p_definition_code and xtdv.definition_code = xsr.definition_code and gps.ledger_id = xtdv.ledger_id and gps.start_date >= nvl(:p_from_date,gps.start_date+ 1) and gps.end_date <= nvl(:p_as_of_date,gps.end_date+ 1) and nvl(gps.adjustment_period_flag,'N')='N' &gcck_join and not exists ( select null from xla_tb_defn_details xtdd where xtdd.definition_code = xtdv.definition_code and xtdd.code_combination_id is not null ) and exists ( select /*+ no_unnest */ 1 from xla_trial_balances xtb where xtb.code_combination_id = gcck.code_combination_id and xtb.definition_code = xtdv.definition_code ) ) select /*+ leading(xtd,l,h,gcck) parallel(xtd) pq_distribute(l,broadcast,none) use_nl(l,gcck,h) parallel(l) parallel(h) parallel(gcck) */ gl.name ledger, gcck.concatenated_segments account_segments, xtd.period_name, gjs.user_je_source_name je_source, gjc.user_je_category_name je_category, sum(nvl(gjl.accounted_cr, 0))- sum(nvl(gjl.accounted_dr,0)) non_ap_amount from gl_ledgers gl, gl_je_sources gjs, gl_je_categories gjc, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations_kfv gcck, xtd where gl.ledger_id = xtd.ledger_id and gjs.je_source_name = gjh.je_source and gjc.je_category_name = gjh.je_category and gjl.code_combination_id = gcck.code_combination_id and gjl.code_combination_id = xtd.code_combination_id and gjl.ledger_id = xtd.ledger_id and gjl.ledger_id = :p_ledger_id and gjl.period_name = xtd.period_name and gjh.je_source <> 'Payables' and gjh.je_header_id = gjl.je_header_id and gjh.ledger_id = gjl.ledger_id and gjh.actual_flag = 'A' and gjh.status = 'P' and gjl.effective_date between nvl(:p_from_date, gjl.effective_date ) and nvl(:p_as_of_date, gjl.effective_date + 1 ) group by gl.name, gcck.concatenated_segments, xtd.period_name, gjs.user_je_source_name, gjc.user_je_category_name order by 1,2,3,4 Y SQLAP #,##0.00 US XLA Trial Balance - Non AP Journal Amounts XLA Trial Balance - Non AP Sourced Journal Amounts XLA Trial Balance Analysis &gcck_join :p_as_of_date :p_definition_code :p_from_date :p_ledger_id 1 -20 :p_from_date Date select to_date('01-01-1950','DD-MM-YYYY') from dual US From Date 2 -10 :p_ledger_id Number select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xtdv.definition_code = :$flex$.report_definition US Ledger ID 3 &gcck_join select replace('and nvl(gcck.$segment$,''0'') between nvl(nvl(xsr.$segment$_from, gcck.$segment$),''0'') and nvl(nvl(xsr.$segment$_to, gcck.$segment$),''0'') ' ,'$segment$',fifs.application_column_name) from gl_ledgers gl, fnd_id_flex_segments fifs where gl.ledger_id = :p_ledger_id and fifs.application_id = 101 and fifs.id_flex_code = 'GL#' and fifs.id_flex_num = gl.chart_of_accounts_id and fifs.enabled_flag = 'Y' US Ledger ID 4 20 :p_definition_code LOV custom Y select xtdv.definition_code value, xtdv.name description from xla_tb_definitions_vl xtdv where xtdv.enabled_flag='Y' and exists (select null from xla_trial_balances xtb where xtb.definition_code = xtdv.definition_code) order by xtdv.definition_code Y AR تعريف التقرير D Auswertungsdefinition F Définition de l'état US Report Definition ZHS 报表定义 5 30 :p_as_of_date Date Y US As of Date :$flex$.report_definition Report Definition Ledger ID 386824FEE925CE2EBA0F05832978E434 Pivot: Default Template DEVELOPER 2 ACCOUNT_SEGMENTS 5 JE_CATEGORY 4 JE_SOURCE 1 LEDGER 6 NON_AP_AMOUNT 3 PERIOD_NAME ACCOUNT_SEGMENTS ROW 2 JE_CATEGORY ROW 5 JE_SOURCE ROW 4 LEDGER ROW 1 NON_AP_AMOUNT VALUE 1 SUM PERIOD_NAME ROW 3 S Site 386824FEE925CE2EBA0F05832978E434