Introduction:

This Post illustrates the steps required to fetch data from AR to GL  in Oracle EBS 11i.

 

Script

SELECT je_header_id, line_number, trx_number, item_number,
customer_number, customer_name, SOURCE, CATEGORY, batch_name,
transaction_date, posted_date, period_name, currency_code,
account_number, description
FROM (SELECT rcta.trx_number, rcta.customer_trx_id, gh.je_header_id,
ac.customer_number, ac.customer_name, gh.je_source SOURCE,
gh.je_category CATEGORY, gh.NAME batch_name,
TO_CHAR (gh.default_effective_date,
‘DD-MON-YYYY’
) transaction_date,
TO_CHAR (gh.posted_date, ‘DD-MON-YYYY’) posted_date,
gh.period_name period_name, gh.currency_code currency_code,
gl.je_line_num line_number,
gcc.segment1
|| ‘.’
|| gcc.segment2
|| ‘.’
|| gcc.segment3
|| ‘.’
|| gcc.segment4
|| ‘.’
|| gcc.segment5
|| ‘.’
|| gcc.segment6 account_number,
gh.description description, gl.accounted_dr debits,
gl.accounted_cr credits, rctla.inventory_item_id,
(SELECT segment1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
rctla.inventory_item_id
AND msib.organization_id = rctla.warehouse_id)
item_number
FROM gl_je_headers gh,
gl_je_lines gl,
gl_code_combinations gcc,
fnd_user fu,
gl_sets_of_books gsob,
gl_import_references gir,
ra_customer_trx_all rcta,
ra_cust_trx_line_gl_dist_all rctlg,
ra_customer_trx_lines_all rctla,
ar_customers ac
WHERE gh.je_header_id = gl.je_header_id
AND gl.code_combination_id = gcc.code_combination_id
AND gh.last_updated_by = fu.user_id
AND gh.period_name IN (‘JUN-19’, ‘JUL-19’)
AND gh.status = ‘P’
AND gh.set_of_books_id = gsob.set_of_books_id
AND gsob.set_of_books_id = 1
AND gh.je_source = ‘Receivables’
AND gir.je_header_id = gh.je_header_id
AND gir.je_line_num = gl.je_line_num
AND gir.je_batch_id = gh.je_batch_id
AND TO_CHAR (gir.reference_2) = TO_CHAR (rcta.customer_trx_id)
AND TO_CHAR (gir.reference_3) =
TO_CHAR (rctlg.cust_trx_line_gl_dist_id)
AND rcta.customer_trx_id = rctlg.customer_trx_id
AND rctla.customer_trx_line_id = rctlg.customer_trx_line_id
AND rctlg.customer_trx_id = rctla.customer_trx_id
AND rctla.customer_trx_id = rcta.customer_trx_id
AND rcta.bill_to_customer_id = ac.customer_id(+)
ORDER BY gh.je_header_id, gl.je_line_num)

Queries

Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search