R12 XLA_TRANSACTION_ENTITIES查询

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