Introduction
This Post is about to Receivables to GL Link in 11i.
SELECT
je_header_id, line_number, trx_number, myrank, item_number,customer_number, customer_name, SOURCE, CATEGORY, batch_name,transaction_date, posted_date, period_name, currency_code,account_number, description,
CASE
WHEN my rank = 1
THEN debits
ELSE NULL
END debits, CASE
WHEN my rank = 1
THEN credits
ELSE NULL
END credits
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,
DENSE_RANK () OVER (PARTITION BY gh.je_header_id, gl.je_line_num ORDER BY rctlg.cust_trx_line_gl_dist_id)
AS myrank
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 = :p_period
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);
Summary
This Post described the script Receivables to GL Link in 11i in Oracle.
What we expect in the script.
This script helps us to comprehend how Receivables to GL Link in 11i. Couple of tables which is being used in the scripts are gl_je_headers,gl_je_lines, gl_code_combinations,fnd_user, gl_sets_of_books,gl_import_references,ra_customer_trx_all,ar_customers, ra_cust_trx_line_gl_dist_all,ra_customer_trx_lines_all etc,
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
