This query provide the details of GL Journal Reconciliation details
SELECT c.name ledger_name,
a.name journal_name,
e.request_id,
e.GROUP_ID,
a.je_batch_id,
(SELECT INITCAP (ppf.full_name)
FROM per_all_people_f ppf, fnd_user fu
WHERE fu.user_id = e.posted_by AND ppf.person_id = fu.employee_id
AND SYSDATE BETWEEN
ppf.effective_start_date
AND ppf.effective_start_date)
posted_by,
a.accrual_rev_flag,
a.accrual_rev_status,
a.je_category user_je_category_name,
a.je_source user_je_source_name,
d.segment1 company,
a.period_name,
DECODE (e.status, 'P', 'Posted', 'U', 'Unposted') unposted_status,
TO_DATE (TRUNC (a.posted_date), 'DD-MM-YYYY hh24:mi:ss') posted_date,
(SELECT user_name
FROM fnd_user
WHERE user_id = e.posted_by)
user_name,
b.je_line_num,
( d.segment1
|| '.'
|| d.segment2
|| '.'
|| d.segment3
|| '.'
|| d.segment4
|| '.'
|| d.segment5
|| '.'
|| d.segment6
|| '.'
|| d.segment7
|| '.'
|| d.segment8
|| '.'
|| d.segment9)
concatenated_segments,
c.currency_code,
b.entered_dr,
b.entered_cr,
b.accounted_dr,
b.accounted_cr,
DECODE (c.currency_code,
'USD', b.entered_dr,
b.entered_dr *
currency_conversion_rate)
entered_dr_in_usd,
DECODE (c.currency_code,
'USD', b.entered_cr,
b.entered_cr *
currency_conversion_rate)
entered_cr_in_usd,
DECODE (c.currency_code,
'USD', b.accounted_dr,
b.accounted_dr *
currency_conversion_rate)
accounted_dr_in_usd,
DECODE (c.currency_code,
'USD', b.accounted_cr,
b.accounted_cr *
currency_conversion_rate)
accounted_cr_in_usd
FROM apps.gl_je_headers a,
apps.gl_je_lines b,
apps.gl_ledgers c,
apps.gl_code_combinations d,
apps.gl_je_batches e
WHERE a.je_header_id = b.je_header_id
AND a.ledger_id = c.ledger_id
AND b.code_combination_id = d.code_combination_id
AND a.je_batch_id = e.je_batch_id
AND e.chart_of_accounts_id = c.chart_of_accounts_id
-- and rownum<100
ORDER BY a.je_source, a.name, b.je_line_num
0 comments:
Post a Comment