收款 付款 到总帐
XLA_TRANSACTION_ENTITIES是R12关联会计分录跟业务事务处理数据的一个表，今天我用
select * from XLA_TRANSACTION_ENTITIES xte
where xte.ledger_id = 1078
查询没数据，但1078的确是有数据的，后来想想可能是这个表做了policy的安全机制处理，于是用
begin
mo_global.set_policy_context('S',8092);
end;
作了初始化，8092是跟1078对应的一个ou,这样就能把数据查询出来了
例子:
begin
mo_global.set_policy_context('S',8092);
end;
select xah.event_type_code
,xal.accounting_class_code
,xal.ae_line_num
,fnd_flex_ext.get_segs('SQLGL','GL#',gl.chart_of_accounts_id,xal.code_combination_id) accout_seg
,xla_oa_functions_pkg.get_ccid_description(gl.chart_of_accounts_id,xal.code_combination_id) accout_desc
,xal.accounted_dr
,xal.accounted_cr
from xla_ae_headers xah
,xla_ae_lines xal
,xla_events xe
,XLA_TRANSACTION_ENTITIES xte
,gl_ledgers gl
where xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.application_id = xe.application_id
and xah.application_id = xte.application_id
and xah.entity_id = xte.entity_id
and xah.entity_id = xe.entity_id
and xah.event_id = xe.event_id
and xte.ledger_id = gl.ledger_id
and xte.ledger_id = 1078
and xte.entity_code = 'AP_INVOICES'
and nvl(xte.source_id_int_1,-99) = 218300;
218300是一张AP发票的invoice id,这个语句是查询此张发票的会计分录
select xah.event_type_code
,xal.accounting_class_code
,xal.ae_line_num
,fnd_flex_ext.get_segs('SQLGL','GL#',gl.chart_of_accounts_id,xal.code_combination_id) accout_seg
,xla_oa_functions_pkg.get_ccid_description(gl.chart_of_accounts_id,xal.code_combination_id) accout_desc
,xal.accounted_dr
,xal.accounted_cr
,gjh.je_category
,gjh.je_source
,gjb.name
,gjl.je_line_num
,xal.gl_sl_link_id
from xla_ae_headers xah
,xla_ae_lines xal
,xla_events xe
,XLA_TRANSACTION_ENTITIES xte
,gl_ledgers gl
,gl_import_references gir
,gl_je_headers gjh
,gl_je_lines gjl
,gl_je_batches gjb
where xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.application_id = xe.application_id
and xah.application_id = xte.application_id
and xah.entity_id = xte.entity_id
and xah.entity_id = xe.entity_id
and xah.event_id = xe.event_id
and xte.ledger_id = gl.ledger_id
and xte.ledger_id = 1078
and xte.entity_code = 'AP_INVOICES'
and nvl(xte.source_id_int_1,-99) = 218300
and xal.gl_sl_link_id = gir.gl_sl_link_id(+)
and xal.gl_sl_link_table = gir.gl_sl_link_table(+)
and gir.je_header_id = gjh.je_header_id(+)
and gir.je_header_id = gjl.je_header_id(+)
and gir.je_batch_id = gjb.je_batch_id(+)
and gir.je_line_num = gjl.je_line_num(+);
会计分录跟总帐关联.
select aca.check_number
,aca.check_date
,aca.amount
,xah.gl_transfer_status_code
,xal.ae_line_num
,fnd_flex_ext.get_segs(application_short_name => 'SQLGL',key_flex_code => 'GL#',structure_number => gl.chart_of_accounts_id
,combination_id => xal.code_combination_id) account_segment
,xla_oa_functions_pkg.get_ccid_description(p_coa_id => gl.chart_of_accounts_id,p_ccid => xal.code_combination_id) account_desc
,xal.accounted_dr
,xal.accounted_cr
,gjb.name batch_name
,gjh.name journal_name
,gjl.je_line_num
from xla_ae_headers xah
,xla_ae_lines xal
,xla_transaction_entities xte
,xla_events xe
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledgers gl
,ap_checks_all aca
where xah.ledger_id = xte.ledger_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.entity_id = xte.entity_id
and xah.application_id = xte.application_id
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xah.entity_id = xe.entity_id
and xal.gl_sl_link_id = gir.gl_sl_link_id(+)
and xal.gl_sl_link_table = gir.gl_sl_link_table(+)
and gir.je_batch_id = gjb.je_batch_id(+)
and gir.je_header_id = gjh.je_header_id(+)
and gir.je_header_id = gjl.je_header_id(+)
and gir.je_line_num = gjl.je_line_num(+)
and xte.ledger_id = gl.ledger_id
and xte.entity_code = 'AP_PAYMENTS'
and nvl(xte.source_id_int_1,-99) = aca.check_id
and gl.name = '帐套名'
and aca.check_number = '付款单据编号';
--AP付款会计分录追溯到总帐
select rcta.trx_number
,rcta.trx_date
,xah.gl_transfer_status_code
,xal.ae_line_num
,fnd_flex_ext.get_segs(application_short_name => 'SQLGL',key_flex_code => 'GL#',structure_number => gl.chart_of_accounts_id
,combination_id => xal.code_combination_id) account_segment
,xla_oa_functions_pkg.get_ccid_description(p_coa_id => gl.chart_of_accounts_id,p_ccid => xal.code_combination_id) account_desc
,xal.accounted_dr
,xal.accounted_cr
,gjb.name batch_name
,gjh.name journal_name
,gjl.je_line_num
,gjl.accounted_dr gl_accounted_dr
,gjl.accounted_cr gl_accounted_cr
,xe.event_type_code
from xla_ae_headers xah
,xla_ae_lines xal
,xla_transaction_entities xte
,xla_events xe
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledgers gl
,ra_customer_trx_all rcta
where xah.ledger_id = xte.ledger_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.entity_id = xte.entity_id
and xah.application_id = xte.application_id
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xah.entity_id = xe.entity_id
and xal.gl_sl_link_id = gir.gl_sl_link_id(+)
and xal.gl_sl_link_table = gir.gl_sl_link_table(+)
and gir.je_batch_id = gjb.je_batch_id(+)
and gir.je_header_id = gjh.je_header_id(+)
and gir.je_header_id = gjl.je_header_id(+)
and gir.je_line_num = gjl.je_line_num(+)
and xte.ledger_id = gl.ledger_id
and xte.entity_code = 'TRANSACTIONS'
and nvl(xte.source_id_int_1,-99) = rcta.customer_trx_id
and gl.name = '帐套名'
and rcta.trx_number = 'AR发票号'
order by gjb.name,gjh.name,xal.ae_line_num
--AR发票会计分录追溯到总帐
select acra.receipt_number
,acra.receipt_date
,acra.amount
,xah.gl_transfer_status_code
,xal.ae_line_num
,fnd_flex_ext.get_segs(application_short_name => 'SQLGL',key_flex_code => 'GL#',structure_number => gl.chart_of_accounts_id
,combination_id => xal.code_combination_id) account_segment
,xla_oa_functions_pkg.get_ccid_description(p_coa_id => gl.chart_of_accounts_id,p_ccid => xal.code_combination_id) account_desc
,xal.accounted_dr
,xal.accounted_cr
,gjb.name batch_name
,gjh.name journal_name
,gjl.je_line_num
,gjl.accounted_dr gl_accounted_dr
,gjl.accounted_cr gl_accounted_cr
,xe.event_type_code
from xla_ae_headers xah
,xla_ae_lines xal
,xla_transaction_entities xte
,xla_events xe
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledgers gl
,ar_cash_receipts_all acra
where xah.ledger_id = xte.ledger_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id = xal.application_id
and xah.entity_id = xte.entity_id
and xah.application_id = xte.application_id
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xah.entity_id = xe.entity_id
and xal.gl_sl_link_id = gir.gl_sl_link_id(+)
and xal.gl_sl_link_table = gir.gl_sl_link_table(+)
and gir.je_batch_id = gjb.je_batch_id(+)
and gir.je_header_id = gjh.je_header_id(+)
and gir.je_header_id = gjl.je_header_id(+)
and gir.je_line_num = gjl.je_line_num(+)
and xte.ledger_id = gl.ledger_id
and xte.entity_code = 'RECEIPTS'
and nvl(xte.source_id_int_1,-99) = acra.cash_receipt_id
and gl.name = '帐套名'
and acra.receipt_number = 'AR收款号'
order by gjb.name,gjh.name,xal.ae_line_num
