XLA Trial Balance - Non AP Journal Amounts
Description
Run
XLA Trial Balance - Non AP Journal Amounts and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Definition |
|
LOV | |
As of Date |
|
Date |