Shulou(Shulou.com)06/01 Report--

From: https://blog.csdn.net/rfb0204421/article/details/7760697

A/R, A/P, receipt, payment and other documents can generate ERP journals, so how are the relationships between these modules related? We will solve this problem.

The relationship between each module and the general ledger module is mainly related through subledgers.

The following SQL is the relationship between the general ledger and the subledger:

SELECT *

FROM gl_je_headers jh

,gl_je_lines jl

,gl_import_references gir

,xla_ae_lines xal

,xla_ae_headers xah

,xla.xla_transaction_entities xte

WHERE jh.je_header_id = jl.je_header_id

AND jl.je_header_id = gir.je_header_id

AND jl.je_line_num = gir.je_line_num

AND gir.gl_sl_link_id = xal.gl_sl_link_id

AND gir.gl_sl_link_table = xal.gl_sl_link_table

AND xal.applicaiton_id = xah.applicaiton_id

AND xal.ae_header_id = xah.ae_header_id

AND xah.applicaiton_id = xte.application_id

AND xah.entity_id = xte.entity_id

AND xah.entity_code = :p1

AND xah.source_id_int_1 = :p2

where:p1 represents type and:p2 represents ID value. For example, if you want to show the relationship between invoices payable and general ledger, then:p1 is 'AP_INVOICES' and:p2 is the ID of AP_INVOICES_ALL.

For specific association relationships, please refer to table xla_entity_id_mappings.

Note:

Through the SQL above, it is possible to query duplicate code, mainly because there may be some obsolete lines in the xla_ae_lines line. How to determine if a row in xla_ae_lines is obsolete: There is a field in the xla_ae_lines table:displayed_line_number, if this value is less than 0, then the row is obsolete. So when using the SQL above, it is best to add the condition:xla.displayed_line_number > 0.

There is a field gl_transfer_status_code in table xla_ae_headers. If the value of this field is Y, it means that this subledger has been posted. When you need to determine whether to post, you can determine the value of this field.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.