SELECT c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num, te.source_id_int_1, te.application_id, te.entity _id, h.je_source, h.je_category, i.gl_date, s.vendor_name, s.

segment1 as supplier_no, l.event_class_code as event_class, i.invoice_id, ad.invoice_distribution_id, i.invoice_num AS transaction_number, i.invoice_date, initcap(jl.description) description, jl.accounted_dr as debit, jl.accounted_cr as credit, Nvl(Jl.Accounted_Dr, 0) Nvl(Jl.Accounted_Cr, 0) Net_Amount From Apps.Gl_Je_Headers H, apps.gl_je_lines jl, Apps.Gl_Code_Combinations C, apps.gl_import_references r, apps.xla_ae_lines al, Apps.Xla_Ae_headers Ah, apps.xla_distribution_links l, apps.ap_invoices_all i, apps.ap_invoice_distributions_all ad, apps.ap_suppliers s, apps.xla_events e, apps.xla_transaction_entities te where ad.accounting_date between :startdate and :enddate and c.code_combination_id = 6429 and jl.description != GB VAT STANDARD TAX and ad.line_type_lookup_code = ITEM AND jl.je_header_id = h.je_header_id AND jl.code_combination_id = c.code_combination_id and al.gl_sl_link_id = r.gl_sl_link_id and al.ae_header_id = ah.ae_header_id and al.application_id = ah.application_id and ah.application_id = e.application_id and ah.event_id = e.event_id and e.application_id = te.application_id(+) and e.entity_id = te.entity_id(+) AND r.je_header_id = jl.je_header_id AND r.je_line_num = jl.je_line_num AND l.ae_header_id = al.ae_header_id and l.ae_line_num = al.ae_line_num and l.applied_to_source_id_num_1 = i.invoice_id and l.source_distribution_id_num_1 = ad.invoice_distribution_id and ad.invoice_id = i.invoice_id and i.vendor_id = s.vendor_id order by i.gl_date desc --------------------------------------------------------------------------------------------------I am looking for a query to drilldown information from GL to AR in release 12, does anyone has one? select b.name batch_name , b.description batch_description

, b.running_total_accounted_dr batch_total_dr , b.running_total_accounted_cr batch_total_cr , b.status batch_status , b.default_effective_date effective_date , b.default_period_name batch_period_name , b.creation_date , u.user_name batch_created_by , h.je_category , h.je_source , h.period_name je_period_name , h.name journal_name , h.status journal_status , h.creation_date je_created_date , u1.user_name je_created_by , h.description je_description , h.running_total_accounted_dr je_total_dr , h.running_total_accounted_cr je_total_cr , l.je_line_num line_number , l.ledger_id , glcc.concatenated_segments Account , l.entered_dr , l.entered_cr , l.accounted_dr , l.accounted_cr , xlal.unrounded_accounted_dr XLA_unrounded_accounted_dr , xlal.unrounded_accounted_cr XLA_unrounded_accounted_cr , l.description , xlal.code_combination_id , xlal.accounting_class_code , xlal.accounted_dr xlal_accounted_dr

, xlal.accounted_cr xlal_accounted_cr , xlal.description xlal_description , xlal.accounting_date xlal_accounting_date , xlate.entity_code xlate_entity_code , xlate.source_id_int_1 xlate_source_id_int_1 , xlate.source_id_int_2 xlate_source_id_int_2 , xlate.source_id_int_3 xlate_source_id_int_3 , xlate.security_id_int_1 xlate_security_id_int_1 , xlate.security_id_int_2 xlate_security_id_int_2 , xlate.transaction_number xlate_transaction_number 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_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 xlal.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.ae_header_id = xlah.ae_header_id and xlah.event_id = xlae.event_id and xlae.entity_id = xlate.entity_id and xlae.application_id = xlate.application_id -- and xlate.source_id_int_1 = rcvt.transaction_id and h.je_source = 'Receivables' --------------------------------------------------------GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS ( je_batch_id) GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je _header_id) GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFER ENCES (je_header_id, je_line_num) GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (g l_sl_link_table, gl_sl_link_id) XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id) XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (app lication_id, event_id) XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSAC TION_ENTITIES (application_id, entity_id) The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information. The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table [Keywords: gl_import_references, xla_ae_lines, xla_ae_headers, xla_events, drill down, SLA, XLA] I am giving u some Links which may help u to make Link from other tables to GL When Source is Payable then reference_5 of GL_JE_LINES table is the invoice Numb er and reference_2 is the invoice_id and Ref3 is the check_id. When Source is Purchasing then ref5 of GL_JE_LINES table represent the transacti on_id of rcv_transaction When Source is Inventory then check the transaction type. If transaction type is Average cost update then the transaction_id of the mtl_ma terial_transaction_id represents the transaction_id of rcv_transaction_id in rcv _transaction table. If transaction_type is PO Receipt then RCV_TRANSACTION_ID in mtl_material_transa ction_id reprensent the transaction_id of rcv_transaction_id in rcv_transaction table.