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

All kinds of transactions in the modules of receivables, payables, inventory, procurement (receiving) and so on will generate the corresponding general ledger accounting information through the sub-ledger. We can view the corresponding transaction operations and interfaces through the traceability function of the general ledger journal line. So what if they are related?

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

The following SQL describes their relationship:

SELECT *

FROM GL_JE_LINES GJL,-- journal bank

GL_JE_HEADERS GJH,-- journal head

GL_JE_SOURCES_TL GJST,-source of journal

GL_JE_CATEGORIES_TL GJCT,-- journal category

GL_CODE_COMBINATIONS_KFV GCC,-- account portfolio

GL_IMPORT_REFERENCES GIR,-- General ledger import reference information (association between GL and XLA)

XLA.XLA_AE_LINES XAL,-sub-ledger line

XLA.XLA_AE_HEADERS XAH,-sub-ledger head

XLA.XLA_TRANSACTION_ENTITIES XTE-subledger transaction processing entity information

WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME

AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.ENTITY_ID = XTE.ENTITY_ID

AND XAH.LEDGER_ID = XTE.LEDGER_ID

AND XAH.APPLICATION_ID = XTE.APPLICATION_ID

AND GJST.LANGUAGE = 'ZHS'

AND GJCT.LANGUAGE = 'ZHS'

AND XTE.SOURCE_ID_INT_1 =: 1

AND XTE.ENTITY_CODE =: 2

The above SQL is the link between the general ledger journal and the sub-ledger sql, so how are the modules related?

For example, the payable module first we know that the entity_code from the invoice payable should be: AP_INVOICES

We can query the AP_INVOICES through the xla_entity_id_mappings table. The corresponding TRANSACTION_ID_COL_NAME_1 field is INVOICE_ID.

So: 1 parameter should be the INVOICE_ID field of AP_INVOICES_ALL table, and: 2 parameter should be 'AP_INVOICES'

-

And so on: for purchasing (receiving)

: 1 the parameter is the TRANSACTION_ID of RCV_TRANSACTIONS table

: 2 Parameter is RCV_ACCOUNTING_EVENTS

For inventory

: 1 the parameter is the TRANSACTION_ID of MTL_MATERIAL_ transactions table

: 2 Parameter is MTL_ACCOUNTING_EVENTS

Note:

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

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

-

This article comes from the CSDN blog of Diego refueling. The full text address can be found at https://blog.csdn.net/sept915/article/details/21229577?utm_source=copy.

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.