ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN CA84F9DBA2FBAF43E05362FB0905DD14Y 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 SQLAP #,##0.00 US XLA Trial Balance - Manual SLA AP Amounts XLA Trial Balance - Manual SLA AP Amounts XLA Trial Balance Analysis &gcck_join :p_as_of_date :p_definition_code :p_from_date :p_ledger_id 1 -20 :p_from_date Date select to_date('01-01-1950','DD-MM-YYYY') from dual US From Date 2 -10 :p_ledger_id Number select xtdv.ledger_id from xla_tb_definitions_vl xtdv where xtdv.definition_code = :$flex$.report_definition US Ledger ID 3 &gcck_join select replace('and nvl(gcck.$segment$,''0'') between nvl(nvl(xsr.$segment$_from, gcck.$segment$),''0'') and nvl(nvl(xsr.$segment$_to, gcck.$segment$),''0'') ' ,'$segment$',fifs.application_column_name) from gl_ledgers gl, fnd_id_flex_segments fifs where gl.ledger_id = :p_ledger_id and fifs.application_id = 101 and fifs.id_flex_code = 'GL#' and fifs.id_flex_num = gl.chart_of_accounts_id and fifs.enabled_flag = 'Y' US Ledger ID 4 20 :p_definition_code LOV custom Y select xtdv.definition_code value, xtdv.name description from xla_tb_definitions_vl xtdv where xtdv.enabled_flag='Y' and exists (select null from xla_trial_balances xtb where xtb.definition_code = xtdv.definition_code) order by xtdv.definition_code Y AR تعريف التقرير D Auswertungsdefinition F Définition de l'état US Report Definition ZHS 报表定义 5 30 :p_as_of_date Date Y US As of Date 897D6627E53280E7BF30845C8E7116EA Pivot: Default Template DEVELOPER 2 ACCOUNT_SEGMENTS 1 LEDGER 4 MANUAL_SLA_AMOUNT 3 PERIOD_NAME ACCOUNT_SEGMENTS ROW 2 LEDGER ROW 1 MANUAL_SLA_AMOUNT VALUE 1 SUM PERIOD_NAME ROW 3 S Site 897D6627E53280E7BF30845C8E7116EA