GL SLA Drillback Diagnostic
Description
GL SLA Drillback Diagnostic
Run
GL SLA Drillback Diagnostic and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Ledger |
|
LOV | |
Period |
|
LOV | |
Journal Source |
|
LOV | |
Journal Category |
|
LOV |