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 |
