Drilldown from GL to Receiving Transactions

This query gives you information from GL, XLA and Receiving Transactions Table. I used this query when I was debugging reconciliation issues between GL and SLA entries and this query was really very very useful.

SELECT b.NAME                   je_batch_name,

b.description                   je_batch_description,

b.running_total_accounted_dr    je_batch_total_dr,

b.running_total_accounted_cr    je_batch_total_cr,

b.status                        je_batch_status,

b.default_effective_date        je_batch_effective_date,

b.default_period_name           je_batch_period_name,

b.creation_date                 je_batch_creation_date,

u.user_name                     je_batch_created_by,

h.je_category                   je_header_category,

h.je_source                     je_header_source,

h.period_name                   je_header_period_name,

h.NAME                          je_header_journal_name,

h.status                        je_header_journal_status,

h.creation_date                 je_header_created_date,

u1.user_name                    je_header_created_by,

h.description                   je_header_description,

h.running_total_accounted_dr    je_header_total_acctd_dr,

h.running_total_accounted_cr    je_header_total_acctd_cr,

l.je_line_num                   je_lines_line_number,

l.ledger_id                     je_lines_ledger_id,

glcc.concatenated_segments      je_lines_ACCOUNT,

l.entered_dr                    je_lines_entered_dr,

l.entered_cr                    je_lines_entered_cr,

l.accounted_dr                  je_lines_accounted_dr,

l.accounted_cr                  je_lines_accounted_cr,

l.description                   je_lines_description,

glcc1.concatenated_segments     xla_lines_account,

xlal.accounting_class_code      xla_lines_acct_class_code,

xlal.accounted_dr               xla_lines_accounted_dr,

xlal.accounted_cr               xla_lines_accounted_cr,

xlal.description                xla_lines_description,

xlal.accounting_date            xla_lines_accounting_date,

xlate.entity_code               xla_trx_entity_code,

xlate.source_id_int_1           xla_trx_source_id_int_1,

xlate.source_id_int_2           xla_trx_source_id_int_2,

xlate.source_id_int_3           xla_trx_source_id_int_3,

xlate.security_id_int_1         xla_trx_security_id_int_1,

xlate.security_id_int_2         xla_trx_security_id_int_2,

xlate.transaction_number        xla_trx_transaction_number,

rcvt.transaction_type           rcv_trx_transaction_type,

rcvt.transaction_date           rcv_trx_transaction_date,

rcvt.quantity                   rcv_trx_quantity,

rcvt.shipment_header_id         rcv_trx_shipment_header_id,

rcvt.shipment_line_id           rcv_trx_shipment_line_id,

rcvt.destination_type_code      rcv_trx_destination_type_code,

rcvt.po_header_id               rcv_trx_po_header_id,

rcvt.po_line_id                 rcv_trx_po_line_id,

rcvt.po_line_location_id        rcv_trx_po_line_location_id,

rcvt.po_distribution_id         rcv_trx_po_distribution_id,

rcvt.vendor_id                  rcv_trx_vendor_id,

rcvt.vendor_site_id             rcv_trx_vendor_site_id

FROM                            

gl_je_batches                   b,

gl_je_headers                   h,

gl_je_lines                     l,

fnd_user                        u,

fnd_user                        u1,

gl_code_combinations_kfv        glcc,

gl_code_combinations_kfv        glcc1,

gl_import_references            gir,

xla_ae_lines                    xlal,

xla_ae_headers                  xlah,

xla_events                      xlae,

xla.xla_transaction_entities    xlate,

rcv_transactions                rcvt

WHERE 

b.created_by                    = u.user_id

AND h.created_by                = u1.user_id

AND b.je_batch_id               = h.je_batch_id

AND h.je_header_id              = l.je_header_id

AND l.code_combination_id       = glcc.code_combination_id

AND l.je_header_id              = gir.je_header_id

AND l.je_line_num               = gir.je_line_num

AND gir.gl_sl_link_table        = xlal.gl_sl_link_table

AND gir.gl_sl_link_id           = xlal.gl_sl_link_id

AND xlal.application_id         = xlah.application_id

AND xlal.ae_header_id           = xlah.ae_header_id

AND xlal.code_combination_id    = glcc1.code_combination_id

AND xlah.application_id         = xlae.application_id

AND xlah.event_id               = xlae.event_id

AND xlae.application_id         = xlate.application_id

AND xlae.entity_id              = xlate.entity_id

AND xlate.source_id_int_1       = rcvt.transaction_id

AND h.je_category               = 'Receiving'

AND b.default_period_name       = '01_APR-2009'

ORDER BY h.je_category;

[Keywords: gl_import_references, xla_ae_lines, xla_ae_headers, xla_events,