INV GL: SQL Query to link inventory material transaction with GL journal entry

The below query used to link the material transactions in the Inventory to the journals entries in the General Ledger, ofcourse you can modify it to fit your requierments.


SELECT DISTINCT glh.*
           FROM xla_transaction_entities_upg xte,
                xla_events xe,
                xla_distribution_links xdl,
                mtl_transaction_accounts mta,
                xla_ae_headers xah,
                xla_ae_lines xal,
                gl_import_references gir,
                gl_je_headers glh


          WHERE 1 = 1
            AND xte.source_id_int_1 = &transaction_id
            AND xte.entity_id = xe.entity_id
            AND mta.transaction_id = xte.source_id_int_1
            AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
            AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
            AND xdl.ae_header_id = xah.ae_header_id
            AND xal.ae_header_id = xdl.ae_header_id
            AND xal.ae_header_id = xah.ae_header_id
            AND gir.gl_sl_link_table = 'XLAJEL'
            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.je_header_id = glh.je_header_id;