XLA Trial Balance - Non AP Journal Amounts

Description
Categories: XLA Trial Balance Analysis
XLA Trial Balance - Non AP Sourced Journal Amounts

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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