AR Useful Query

Sharing few queries Which i have came across while browsing :-


TRACE WHICH AR INVOICE IS LINKED WITH WHICH GL ENTRY.

Linking Table between AR to GL :

In R12, try using the query below:

SELECT b.NAME batch_name,
b.description batch_description,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.NAME journal_name,
h.status journal_status,
h.description je_description,
l.je_line_num line_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
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,
ra_customer_trx_all rct,
WHERE 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 h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =''





11i

select gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, trunc(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
from apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
where /*gjh.period_name IN ('OCT-2008','NOV-2008')
and */glcc.code_combination_id = gje.code_combination_id
and gjh.je_batch_id = gjb.je_batch_id
and gjh.je_header_id = gje.je_header_id
and gjh.period_name = gjb.default_period_name
and gjh.period_name = gje.period_name
and gjjlv.period_name = gjh.period_name
and gjjlv.je_batch_id = gjh.je_batch_id
and gjjlv.je_header_id = gjh.je_header_id
and gjjlv.line_je_line_num = gje.je_line_num
and gjjlv.line_code_combination_id = glcc.code_combination_id
and gjjlv.line_reference_4 = rcta.trx_number
and rcta.cust_trx_type_id = rctype.cust_trx_type_id
and rcta.org_id = rctype.org_id
and ra.customer_id = rcta.bill_to_customer_id

Happy Reading.....................