XLA Trial Balance - Manual SLA AP Amounts
Description
Run
XLA Trial Balance - Manual SLA AP Amounts and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Definition |
|
LOV | |
As of Date |
|
Date |