EBS会计事件

--------------------------------------------------------------------------

INV：

--查询是否创建分录

--如果比较慢加上强制索引语句：/*+index(xah XLA_AE_HEADERS_N6)*/

SELECT xte.application_id, xal.*

  FROM mtl_material_transactions    mmt,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE mmt.transaction_id = xte.source_id_int_1

   AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'

   AND xte.application_id = 707

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND xe.event_id = xah.event_id

   AND xe.application_id = xah.application_id

   AND xah.ae_header_id = xal.ae_header_id

   AND xah.application_id = xal.application_id

   AND mmt.transaction_id = 154370331

   AND 1 = 1;

--查询对应的日记账数据

SELECT gjh.*

  FROM mtl_material_transactions    mmt,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal,

       gl_import_references         gir,

       gl_je_lines                  gjl,

       gl_je_headers                gjh

 WHERE mmt.transaction_id = xte.source_id_int_1

   AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'

   AND xte.application_id = 707

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND xe.event_id = xah.event_id

   AND xe.application_id = xah.application_id

   AND xah.ae_header_id = xal.ae_header_id

   AND xah.application_id = xal.application_id

   AND xal.gl_sl_link_table = gir.gl_sl_link_table

   AND xal.gl_sl_link_id = gir.gl_sl_link_id

   AND gir.je_header_id = gjl.je_header_id

   AND gir.je_line_num = gjl.je_line_num

   AND gjl.je_header_id = gjh.je_header_id

   AND mmt.transaction_id = 209703296;

----强制索引加快速度

SELECT /*+index(xah XLA_AE_HEADERS_N6)*/

 mmt.transaction_id

  FROM mtl_material_transactions    mmt,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE mmt.transaction_id = nvl(xte.source_id_int_1, (-99))

   AND xte.ledger_id =

       (SELECT gl.ledger_id FROM gl_ledgers gl WHERE gl.name = 'M')

   AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'

   AND xte.application_id = 707

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND xe.event_status_code IN ('P')

   AND xe.process_status_code IN ('P')

   AND mmt.transaction_date BETWEEN

       to_date('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND

       to_date('2020-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

   AND mmt.transaction_id = 94804321

   AND 1 = 1;

--------------------------------------------------------------------------

FA:事务处理

--查询是否创建分录

SELECT xe.*

  FROM fa_transaction_headers       fth,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE fth.transaction_header_id = xte.source_id_int_1

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 140

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND fth.asset_id = 10029667;

--查询对应的子分类帐

SELECT xal.*

  FROM fa_transaction_headers       fth,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE fth.transaction_header_id = xte.source_id_int_1

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 140

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND xe.event_id = xah.event_id

   AND xe.application_id = xah.application_id

   AND xah.ae_header_id = xal.ae_header_id

   AND xah.application_id = xal.application_id

   AND fth.asset_id = 10029667;

--查询对应的日记账数据

SELECT gjh.*

  FROM fa_transaction_headers       fth,

       xla.xla_transaction