XLA Trial Balance - Manual SLA AP Amounts

with xtd as
 (
  select /*+ materialize */
  distinct
         xtdv.ledger_id,
         xtdd.code_combination_id
  from   xla_tb_defn_details xtdd,
         xla_tb_definitions_vl xtdv
  where xtdd.definition_code = :p_definition_code
  and   xtdv.definition_code = xtdd.definition_code
  and   xtdd.code_combination_id is not null
  union
  select /*+ materialize */
         distinct
         xtdv.ledger_id,
         gcck.code_combination_id
  from
         xla_tb_definitions_vl xtdv,
         xla_tb_def_seg_ranges xsr,
         gl_code_combinations_kfv gcck
  where xtdv.definition_code = :p_definition_code
  and   xtdv.definition_code = xsr.definition_code
  &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
  )
 ) ,
xe as
 (
  select  xe1.application_id , xe1.event_id
  from xla_events xe1
  where xe1.application_id = 200
  and xe1.process_status_code = 'P'
  and xe1.event_type_code = 'MANUAL'
 )
select  /*+ ordered use_nl(xe,h,l,xtd,gcck) no_index(l mis_xla_ae_lines_n1) */
 gl.name ledger,
 gcck.concatenated_segments account_segments,
 xah.period_name,
 sum(nvl(xal.accounted_cr,0))-sum(nvl(xal.accounted_dr,0)) manual_sla_amount
from
 xe,
 xla_ae_headers xah,
 xla_ae_lines xal,
 xtd,
 gl_code_combinations_kfv gcck,
 gl_ledgers gl
where gcck.code_combination_id = xal.code_combination_id
and xah.application_id = 200
and gcck.code_combination_id = xtd.code_combination_id
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = xal.ledger_id
and xah.gl_transfer_status_code='Y'
and xah.accounting_entry_status_code='F'
and xah.event_type_code='MANUAL'
and xah.balance_type_code='A'
and xah.ledger_id = xtd.ledger_id
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id
and gl.ledger_id = xtd.ledger_id
and xah.ledger_id = :p_ledger_id
and xah.accounting_date between :p_from_date and  :p_as_of_date
group by
 gl.name,
 gcck.concatenated_segments,
 xah.period_name
order by 1,2,3