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_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 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 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 fth.asset_id = 10029667;

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

FA:折旧

--查询是否创建分录

SELECT fds.book_type_code, xe.*

  FROM fa_deprn_summary             fds,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE fds.asset_id = xte.source_id_int_1

   AND fds.book_type_code = xte.source_id_char_1

   AND fds.period_counter = xte.source_id_int_2

   AND fds.deprn_run_id = xte.source_id_int_3

   AND xte.entity_code = 'DEPRECIATION'

   AND xte.application_id = 140

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND fds.deprn_source_code <> 'BOOKS'

      --

   AND fds.book_type_code = 'K'

   AND fds.asset_id = 10012604

   AND 1 = 1;

--查询对应的子分类帐

SELECT fds.book_type_code, xe.*

  FROM fa_deprn_summary             fds,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE 1 = 1

   AND fds.deprn_source_code <> 'BOOKS'

   AND fds.asset_id = xte.source_id_int_1

   AND fds.book_type_code = xte.source_id_char_1

   AND fds.period_counter = xte.source_id_int_2

   AND fds.deprn_run_id = xte.source_id_int_3

   AND xte.entity_code = 'DEPRECIATION'

   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 fds.book_type_code = 'K'

   AND fds.asset_id = 10012604

   AND 1 = 1;

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

SELECT fds.book_type_code, xe.*

  FROM fa_deprn_summary             fds,

       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 1 = 1

   AND fds.deprn_source_code <> 'BOOKS'

   AND fds.asset_id = xte.source_id_int_1

   AND fds.book_type_code = xte.source_id_char_1

   AND fds.period_counter = xte.source_id_int_2

   AND fds.deprn_run_id = xte.source_id_int_3

   AND xte.entity_code = 'DEPRECIATION'

   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 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 fds.book_type_code = 'K'

   AND fds.asset_id = 10012604

   AND 1 = 1;

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

AP发票:

--会计事件

SELECT xe.*

  FROM ap_invoices_all aia, xla.xla_transaction_entities xte, xla_events xe

 WHERE aia.invoice_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_INVOICES'

   AND xte.application_id = 200

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND aia.invoice_id = 239624;

--子分类账

SELECT xal.*

  FROM ap_invoices_all              aia,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE aia.invoice_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_INVOICES'

   AND xte.application_id = 200

   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 aia.invoice_id = 239624

   --and xe.event_id = 19425479

   and 1=1;

--子分类账+分配

SELECT xdl.source_distribution_id_num_1,

       xdl.*

  FROM ap_invoices_all              aia,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal,

       xla_distribution_links       xdl

 WHERE aia.invoice_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_INVOICES'

   AND xte.application_id = 200

   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.ae_header_id = xdl.ae_header_id

   AND xal.ae_line_num = xdl.ae_line_num

   AND xal.application_id = xdl.application_id

   AND aia.invoice_id = 239624

   and xe.event_id = 19425479;

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

SELECT gjh.*

  FROM ap_invoices_all              aia,

       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 aia.invoice_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_INVOICES'

   AND xte.application_id = 200

   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 aia.invoice_id = 184400;

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

AP付款：

--查询是否创建分录

SELECT xe.*

  FROM ap_checks_all ac, xla.xla_transaction_entities xte, xla_events xe

 WHERE ac.check_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_PAYMENTS'

   AND xte.application_id = 200

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND ac.check_id = 189088;

--查询对应的子分类帐

SELECT xal.*

  FROM ap_checks_all                ac,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE ac.check_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_PAYMENTS'

   AND xte.application_id = 200

   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 ac.check_id = 189088;

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

SELECT gjh.*

  FROM ap_checks_all                ac,

       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 ac.check_id = xte.source_id_int_1

   AND xte.entity_code = 'AP_PAYMENTS'

   AND xte.application_id = 200

   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 ac.check_id = 189088;

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

PO:

--会计事件

SELECT xe.*

  FROM rcv_transactions             rcv,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE rcv.transaction_id = xte.source_id_int_1

   AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'

   AND xte.application_id = 707

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND rcv.transaction_id IN (208695)

   AND 1 = 1;

--查询对应的子分类帐

SELECT xal.*

  FROM rcv_transactions             rcv,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE rcv.transaction_id = xte.source_id_int_1

   AND xte.entity_code = 'RCV_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 rcv.transaction_id = 1332100;

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

SELECT gjh.*

  FROM rcv_transactions             rcv,

       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 rcv.transaction_id = xte.source_id_int_1

   AND xte.entity_code = 'RCV_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 rcv.transaction_id = 1332100;

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

--AR事务处理:

--会计事件

SELECT xe.*

  FROM ra_customer_trx_all         rct,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE rct.customer_trx_id = xte.source_id_int_1

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 222

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND rct.customer_trx_id = 365045

   AND 1 = 1;

--查询对应的子分类帐

SELECT xal.*

  FROM ra_customer_trx_all         rct,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE rct.customer_trx_id = xte.source_id_int_1

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 222

   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 rct.customer_trx_id = 365045

   AND 1 = 1;

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

SELECT gjh.*

  FROM ra_customer_trx_all         rct,

       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 rct.customer_trx_id = xte.source_id_int_1

   AND xte.entity_code = 'TRANSACTIONS'

   AND xte.application_id = 222

   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 rct.customer_trx_id = 365045;

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

AR收款:

--会计事件

SELECT xe.*

  FROM ar_cash_receipts_all         ar,

       xla.xla_transaction_entities xte,

       xla_events                   xe

 WHERE ar.cash_receipt_id = xte.source_id_int_1

   AND xte.entity_code = 'RECEIPTS'

   AND xte.application_id = 222

   AND xte.application_id = xe.application_id

   AND xte.entity_id = xe.entity_id

   AND ar.cash_receipt_id = 134618

   AND 1 = 1;

--查询对应的子分类帐

SELECT xal.*

  FROM ar_cash_receipts_all         ar,

       xla.xla_transaction_entities xte,

       xla_events                   xe,

       xla_ae_headers               xah,

       xla_ae_lines                 xal

 WHERE ar.cash_receipt_id = xte.source_id_int_1

   AND xte.entity_code = 'RECEIPTS'

   AND xte.application_id = 222

   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 ar.cash_receipt_id = 134618

   AND 1 = 1;

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

SELECT gjh.*

  FROM ar_cash_receipts_all         ar,

       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 ar.cash_receipt_id = xte.source_id_int_1

   AND xte.entity_code = 'RECEIPTS'

   AND xte.application_id = 222

   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 ar.cash_receipt_id = 134618;

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