GL SLA Drillback Diagnostic

select
 gl.name ledger
,gjh.period_name
,gjs.user_je_source_name
,gjc.user_je_category_name
,gir.gl_sl_link_table
,gir.gl_sl_link_id
,xah.application_id xah_appl_id
,xah.gl_transfer_status_code
,xah.accounting_entry_status_code
,xe.event_type_code
,xe.event_id
,xte.application_id xte_appl_id
,xte.entity_code
,xte.source_id_int_1
,xal.accounting_class_code
,xdl.accounting_line_code
,xdl.accounting_line_type_code
,xdl.line_definition_code
,xdl.event_class_code
,xdl.event_type_code dist_event_type_code
,xdl.source_distribution_type
,xdl.source_distribution_id_num_1
-- 707 - Cost Management
,wt.transaction_id                 wip_transaction_id
,we.wip_entity_name                wip_entity_name
,wt.project_id                     wip_project_id
--,wta.wip_sub_ledger_id             wip_sub_ledger_id
--,wt2.transaction_id                wta_wip_transaction_id
--,we2.wip_entity_name               wta_wip_entity_name
--,wt2.project_id                    wta_project_id
,rrsl.rcv_sub_ledger_id            rrsl_rcv_sub_ledger_id
,rrsl.rcv_transaction_id           rrsl_rcv_transaction_id
,rt.transaction_id                 rrsl_transaction_id
,rt.wip_entity_id                  rrsl_wip_entity_id
,rt.wip_line_id                    rrsl_wip_line_id
,rt.wip_repetitive_schedule_id     rrsl_wip_repetitive_sched_id
,rt.wip_operation_seq_num          rrsl_wip_operation_seq_num
,rt.wip_resource_seq_num           rrsl_wip_resource_seq_num
,rt.project_id                     rrsl_project_id
,we3.wip_entity_name               rrsl_wip_entity_name
-- 275 - Projects
,ppa.segment1                      pa_project
,peia.expenditure_type             pa_expenditure_type
-- 222 AR
,rcta.customer_trx_id              ar_customer_trx_id
,rcta.interface_header_context     ar_interface_header_context
,rcta.interface_header_attribute1  ar_interface_header_att1
,rbsa.name                         ar_batch_source
-- 555 OPM
,gxeh.header_id                    gxeh_header_id
,gxeh.reference_no                 gxeh_reference_no
,gxeh.event_id                     gxeh_event_id
,gxeh.entity_code                  gxeh_entity_code
,gxeh.event_class_code             gxeh_event_class_code
,gxeh.event_type_code              gxeh_event_type_code
,gxeh.transaction_id               gxeh_transaction_id
,gxel.line_id                      gxel_line_id
,gxel.event_id                     gxel_event_id
,grat.accounting_txn_id            grat_accounting_txn_id
,grat.rcv_transaction_id           grat_rcv_transaction_id
,rt2.transaction_id                grat_transaction_id
,rt2.wip_entity_id                 grat_wip_entity_id
,rt2.wip_line_id                   grat_wip_line_id
,rt2.wip_repetitive_schedule_id    grat_wip_repetitive_sched_id
,rt2.wip_operation_seq_num         grat_wip_operation_seq_num
,rt2.wip_resource_seq_num          grat_wip_resource_seq_num
,rt2.project_id                    grat_project_id
,we4.wip_entity_name               grat_wip_entity_name
,mmt.project_id                    grat_mmt_project_id
-- 140 Assets
,case 
 when xte.application_id = 140 and xte.entity_code = 'TRANSACTIONS'
 then (select fab.asset_number from fa_additions_b fab,fa_transaction_headers fth where fth.asset_id=fab.asset_id and fth.transaction_header_id=xte.source_id_int_1)
 when xte.application_id = 140 and xte.entity_code = 'DEPRECIATION'
 then (select fab.asset_number from fa_additions_b fab where fab.asset_id=xte.source_id_int_1)
 end asset_number 
,case 
 when xte.application_id = 140 and xte.entity_code = 'TRANSACTIONS'
 then (select fab.asset_number from fa_additions_b fab,fa_transaction_headers fth where fth.asset_id=fab.asset_id and fth.transaction_header_id=xte.source_id_int_1)
 when xte.application_id = 140 and xte.entity_code = 'DEPRECIATION'
 then (select fab.asset_number from fa_additions_b fab, fa_deprn_detail fdd where fab.asset_id=fdd.asset_id and fdd.asset_id=xte.source_id_int_1 and fdd.period_counter=xte.source_id_int_2 and fdd.deprn_run_id=xte.source_id_int_3 and rownum=1)
 end asset_number2
from
  gl_ledgers                   gl
, gl_je_sources                gjs
, gl_je_categories             gjc
, gl_je_headers                gjh
, gl_je_lines                  gjl
, gl_import_references         gir
, gl_code_combinations         gcc
, xla.xla_ae_lines             xal
, xla.xla_ae_headers           xah
, xla.xla_events               xe
, xla.xla_transaction_entities xte
, xla.xla_distribution_links   xdl
-- 707 - Cost Management
, wip_transactions             wt
, wip_entities                 we
--, wip_transaction_accounts     wta
--, wip_transactions             wt2
--, wip_entities                 we2
, rcv_receiving_sub_ledger     rrsl
, rcv_transactions             rt
, wip_entities                 we3
-- 275 - Projects
, pa_expenditure_items_all     peia
, pa_projects_all              ppa
-- 222 AR
, ar_adjustments_all           aaa
, ra_customer_trx_all          rcta
, ra_batch_sources_all         rbsa
-- 555 OPM
, gmf_xla_extract_lines        gxel
, gmf_xla_extract_headers      gxeh
, gmf_rcv_accounting_txns      grat
, rcv_transactions             rt2
, wip_entities                 we4
, mtl_material_transactions    mmt
where
    gjs.je_source_name           = gjh.je_source
and gjc.je_category_name         = gjh.je_category
and gjl.je_header_id             = gjh.je_header_id
and gcc.code_combination_id      = gjl.code_combination_id
--
and gir.je_header_id (+)         = gjl.je_header_id
and gir.je_line_num  (+)         = gjl.je_line_num
--
and xal.gl_sl_link_id (+)        = gir.gl_sl_link_id
and xal.gl_sl_link_table (+)     = gir.gl_sl_link_table
and xah.ae_header_id (+)         = xal.ae_header_id
and xah.application_id (+)       = xal.application_id
and xe.application_id (+)        = xah.application_id
and xe.event_id (+)              = xah.event_id
and xte.application_id (+)       = xah.application_id
and xte.entity_id (+)            = xah.entity_id
and xdl.ae_header_id (+)         = xal.ae_header_id
and xdl.ae_line_num (+)          = xal.ae_line_num
-- 707 - Cost Management
and wt.transaction_id (+)        = case when xte.application_id = 707 then xte.source_id_int_1 end
and we.wip_entity_id (+)         = wt.wip_entity_id
--and wta.wip_sub_ledger_id (+)    = case when xdl.application_id = 707 and xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS' then xdl.source_distribution_id_num_1 end
--and wt2.transaction_id (+)       = wta.transaction_id
--and we2.wip_entity_id            = wt2.wip_entity_id
and rrsl.rcv_sub_ledger_id (+)   = case when xdl.application_id = 707 and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' then xdl.source_distribution_id_num_1 end
and rt.transaction_id (+)        = rrsl.rcv_transaction_id
and we3.wip_entity_id (+)        = rt.wip_entity_id
-- 275 - Projects
and peia.expenditure_item_id (+) = case when xte.application_id = 275 and xte.entity_code = 'EXPENDITURES' then xte.source_id_int_1 end
and ppa.project_id (+)           = case when xte.application_id = 275 then case xte.entity_code when 'REVENUE' then xte.source_id_int_1 when 'EXPENDITURES' then peia.project_id end end
-- 222 AR
and aaa.adjustment_id (+)        = case when xte.application_id = 222 and xte.entity_code = 'ADJUSTMENTS' then xte.source_id_int_1 end
and rcta.customer_trx_id (+)     = case when xte.application_id = 222 then case xte.entity_code when 'TRANSACTIONS' then xte.source_id_int_1 when 'BILLS_RECEIVABLE' then xte.source_id_int_1 when 'ADJUSTMENTS' then aaa.customer_trx_id end end
and rbsa.batch_source_id (+)     = rcta.batch_source_id
and rbsa.org_id (+)              = rcta.org_id
-- 555 OPM
and gxel.line_id (+)             = case when xdl.application_id = 555 then xdl.source_distribution_id_num_1 end
and gxeh.header_id (+)           = gxel.header_id
and grat.accounting_txn_id (+)   = gxeh.transaction_id
and rt2.transaction_id (+)       = grat.rcv_transaction_id
and we4.wip_entity_id (+)        = rt2.wip_entity_id
and mmt.transaction_id (+)       = gxeh.transaction_id
--
and gjh.status                  = 'P'
and gjh.actual_flag             = 'A'
and 1=1