GL Category and subledger entities

Description
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
Ask a question