Payables data in Sub-Ledger Accounting (XLA) - R12

最新推荐文章于 2022-05-31 08:19:51 发布

转载最新推荐文章于 2022-05-31 08:19:51 发布 · 141 阅读

本文介绍如何通过Oracle Sub-Ledger Accounting (XLA) 查询特定应付账款发票(Invoice_id=166014)的支付数据及相关会计事件。提供了用于检索XLA事件、AE头、AE行及分配链接等信息的SQL查询语句。

http://www.shareoracleapps.com/2011/01/payment-data-in-sub-ledger-accounting.html

In this post, we will check the Payment Data related to the Payable INVOICE ( Invoice_id = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

XLA_EVENTS

SELECT

DISTINCT xe .*

FROM ap_invoice_payments_all aip ,

xla_events xe ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xte . application_id = xe . application_id

AND aip . invoice_id = '166014'

AND xte . entity_code = 'AP_PAYMENTS'

AND xte . source_id_int_1 = aip . check_id

AND xte . entity_id = xe . entity_id

ORDER BY

xe . entity_id ,

xe . event_number ;

XLA_AE_HEADERS

SELECT DISTINCT xeh .*

FROM xla_ae_headers xeh ,

ap_invoice_payments_all aip ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xte . application_id = xeh . application_id

AND aip . invoice_id = '166014'

AND xte . entity_code = 'AP_PAYMENTS'

AND xte . source_id_int_1 = aip . check_id

AND xte . entity_id = xeh . entity_id

ORDER BY

xeh . event_id ,

xeh . ae_header_id ASC;

XLA_AE_LINES

SELECT DISTINCT xel .*,

fnd_flex_ext . get_segs ( 'SQLGL' , 'GL#' , '50577' , xel . code_combination_id ) "Account"

FROM xla_ae_lines xel ,

xla_ae_headers xeh ,

ap_invoice_payments_all aip ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xel . application_id = xeh . application_id

AND xte . application_id = xeh . application_id

AND aip . invoice_id = '166014'

AND xel . ae_header_id = xeh . ae_header_id

AND xte . entity_code = 'AP_PAYMENTS'

AND xte . source_id_int_1 = aip . check_id

AND xte . entity_id = xeh . entity_id

ORDER BY

xel . ae_header_id ,

xel . ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl .*

FROM xla_distribution_links xdl ,

xla_ae_headers xeh ,

ap_invoice_payments_all aip ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xdl . application_id = xeh . application_id

AND xte . application_id = xeh . application_id

AND aip . invoice_id = '166014'

AND xdl . ae_header_id = xeh . ae_header_id

AND xte . entity_code = 'AP_PAYMENTS'

AND xte . source_id_int_1 = aip . check_id

AND xte . entity_id = xeh . entity_id

In this post, we will check the Data related to the Payable INVOICE ( Invoice_id = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

XLA_EVENTS

SELECT DISTINCT xe .*

FROM ap_invoices_all ai ,

xla_events xe ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xte . application_id = xe . application_id

AND ai . invoice_id = '166014'

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id

AND xte . entity_id = xe . entity_id

ORDER BY

xe . entity_id ,

xe . event_number ;

XLA_AE_HEADERS

SELECT DISTINCT xeh .*

FROM xla_ae_headers xeh ,

ap_invoices_all ai ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xte . application_id = xeh . application_id

AND ai . invoice_id = '166014'

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id

AND xte . entity_id = xeh . entity_id

ORDER BY

xeh . event_id ,

xeh . ae_header_id ASC;

XLA_AE_LINES

SELECT DISTINCT xel .*,

fnd_flex_ext . get_segs ( 'SQLGL' , 'GL#' , '50577' , xel . code_combination_id ) "Account"

FROM xla_ae_lines xel ,

xla_ae_headers xeh ,

ap_invoices_all ai ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xel . application_id = xeh . application_id

AND xte . application_id = xeh . application_id

AND ai . invoice_id = '166014'

AND xel . ae_header_id = xeh . ae_header_id

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id

AND xte . entity_id = xeh . entity_id

ORDER BY

xel . ae_header_id ,

xel . ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl .*

FROM xla_distribution_links xdl ,

xla_ae_headers xeh ,

ap_invoices_all ai ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND xdl . application_id = xeh . application_id

AND xte . application_id = xeh . application_id

AND ai . invoice_id = '166014'

AND xdl . ae_header_id = xeh . ae_header_id

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id

AND xte . entity_id = xeh . entity_id

ORDER BY

xdl . event_id ,

xdl . a_header_id ,

xdl . ae_line_num ASC;

XLA_TRANSACTION_ENTITIES

SELECT DISTINCT xte .*

FROM ap_invoices_all ai ,

xla . xla_transaction_entities xte

WHERE xte . application_id = 200

AND ai . invoice_id = '166014'

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id ;

XLA_ACCOUNTING_ERRORS

SELECT DISTINCT xae .*

FROM ap_invoices_all ai ,

xla_events xe ,

xla . xla_transaction_entities xte ,

xla_accounting_errors xae

WHERE xte . application_id = 200

AND xae . application_id = xte . application_id

AND xte . application_id = xe . application_id

AND ai . invoice_id = '166014'

AND xe . event_id = xae . event_id

AND xte . entity_code = 'AP_INVOICES'

AND xte . source_id_int_1 = ai . invoice_id

AND xte . entity_id = xe . entity_id ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-705055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-705055/