GL Category and subledger entities

select
count(*) count,
je_source,
user_source,
je_category,
user_category,
event_type,
entity_code
from
(
select
--gl.name ledger,
gjh.je_source,
(select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjh.je_source=gjsv.je_source_name) user_source,
gjh.je_category,
(select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjh.je_category=gjcv.je_category_name) user_category,
gjl.je_header_id,
gjl.je_line_num,
(select xett.name from xla_event_types_tl xett where xte.application_id=xett.application_id and xte.entity_code=xett.entity_code and xe.event_type_code=xett.event_type_code and xett.language=userenv('lang')) event_type,
xte.entity_code,
xe.entity_id,
xe.application_id
from
gl_ledgers gl,
gl_periods gp,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_sources_vl gjsv,
gl_je_categories_vl gjcv,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
where
--aia.vendor_id=:vendor_id and
--gjh.period_name='Apr-07' and
--gp.start_date between :start_date and :end_date and
--gl.name=:ledger_name and
--gjh.je_source in ('Payables','Receivables') and
--gjh.je_category=:je_category and
--gjh.je_source='Payables' and
--gjl.je_header_id=77151 and
--gjl.je_line_num=1 and
1=1 and
--gjl.je_header_id=5908781 and
--gjl.je_line_num=1 and
gl.period_set_name=gp.period_set_name and
gp.adjustment_period_flag='N' and
gp.period_name=gjh.period_name and
gl.ledger_id=gjh.ledger_id and
gjh.status='P' and
gjh.actual_flag='A' and
gjb.je_batch_id=gjh.je_batch_id and
gjh.je_source=gjsv.je_source_name(+) and
gjh.je_category=gjcv.je_category_name(+) and
gjh.je_header_id=gjl.je_header_id and
gjl.je_header_id=gir.je_header_id and
gjl.je_line_num=gir.je_line_num and
gir.gl_sl_link_id=xal.gl_sl_link_id and
gir.gl_sl_link_table=xal.gl_sl_link_table and
xal.ae_header_id=xah.ae_header_id and
xal.application_id=xah.application_id and
xah.gl_transfer_status_code='Y' and
xah.accounting_entry_status_code='F' and
xah.event_id=xe.event_id and
xah.application_id=xe.application_id and
xe.entity_id=xte.entity_id(+) and
xe.application_id=xte.application_id(+)
)
group by
je_source,
user_source,
je_category,
user_category,
event_type,
entity_code
order by count(*) desc

select count(*) count, je_source, user_source, je_category, user_category, event_type, entity_code from ( select --gl.name ledger, gjh.je_source, (select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjh.je_source=gjsv.je_source_name) user_source, gjh.je_category, (select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjh.je_category=gjcv.je_category_name) user_category, gjl.je_header_id, gjl.je_line_num, (select xett.name from xla_event_types_tl xett where xte.application_id=xett.application_id and xte.entity_code=xett.entity_code and xe.event_type_code=xett.event_type_code and xett.language=userenv('lang')) event_type, xte.entity_code, xe.entity_id, xe.application_id from gl_ledgers gl, gl_periods gp, gl_je_batches gjb, gl_je_headers gjh, gl_je_sources_vl gjsv, gl_je_categories_vl gjcv, gl_je_lines gjl, gl_import_references gir, xla_ae_lines xal, xla_ae_headers xah, xla.xla_events xe, xla.xla_transaction_entities xte where --aia.vendor_id=:vendor_id and --gjh.period_name='Apr-07' and --gp.start_date between :start_date and :end_date and --gl.name=:ledger_name and --gjh.je_source in ('Payables','Receivables') and --gjh.je_category=:je_category and --gjh.je_source='Payables' and --gjl.je_header_id=77151 and --gjl.je_line_num=1 and 1=1 and --gjl.je_header_id=5908781 and --gjl.je_line_num=1 and gl.period_set_name=gp.period_set_name and gp.adjustment_period_flag='N' and gp.period_name=gjh.period_name and gl.ledger_id=gjh.ledger_id and gjh.status='P' and gjh.actual_flag='A' and gjb.je_batch_id=gjh.je_batch_id and gjh.je_source=gjsv.je_source_name(+) and gjh.je_category=gjcv.je_category_name(+) and gjh.je_header_id=gjl.je_header_id and gjl.je_header_id=gir.je_header_id and gjl.je_line_num=gir.je_line_num and gir.gl_sl_link_id=xal.gl_sl_link_id and gir.gl_sl_link_table=xal.gl_sl_link_table and xal.ae_header_id=xah.ae_header_id and xal.application_id=xah.application_id and xah.gl_transfer_status_code='Y' and xah.accounting_entry_status_code='F' and xah.event_id=xe.event_id and xah.application_id=xe.application_id and xe.entity_id=xte.entity_id(+) and xe.application_id=xte.application_id(+) ) group by je_source, user_source, je_category, user_category, event_type, entity_code order by count(*) desc