Journal Reconciliation in Oracle Fusion Finance is a task that the client will invariably would do at the end of the day. After all they are in business and would like to match the books at the end of the day. Hopefully query given below will help you do the same against certain GL accounts.
Query for the Journal Reconciliation
SELECT period_name,
accounting_date,
posting_date,
jrnl_cat_desc,
journal_batch,
journal_desc,
MAX(batch_creation_date) batch_creation_date,
MAX(batch_last_update_date) batch_last_update_date,
MAX(last_updated_by_username) last_updated_by_username,
concat_segs,
journal_name,
SUM(amount) amount
FROM (
SELECT gjh.period_name ,
TO_CHAR(xal.ACCOUNTING_DATE , ‘MM/DD/YYYY’) ACCOUNTING_DATE,
TO_CHAR( gjh.POSTED_DATE, ‘MM/DD/YYYY’) POSTING_DATE,
gjb.name Journal_batch,
TO_CHAR( gjb.creation_date , ‘MM/DD/YYYY’) batch_creation_date,
TO_CHAR( gjb.last_update_date , ‘MM/DD/YYYY’) batch_last_update_date,
gjb.last_updated_by last_updated_by_username ,
(NVL(gjl.accounted_cr,0) – NVL(gjl.accounted_dr,0)) Amount ,
gcc.segment1 || ‘.’ || gcc.segment2 || ‘.’
|| gcc.segment3 || ‘.’ || gcc.segment4 || ‘.’
|| gcc.segment5 || ‘.’ || gcc.segment6 || ‘.’
|| gcc.segment7 Concat_Segs,
gcc.financial_category Account_Type,
gjc.description Jrnl_Cat_Desc,
gjh.name Journal_Name,
gjh.description Journal_Desc
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
gl_code_combinations gcc,
gl_je_batches gjb,
gl_je_categories_tl gjc,
xla_ae_lines xal
WHERE 1=1
AND gjh.je_header_id = gjl.je_header_id
and gcc.code_combination_id = gjl.code_combination_id
and gjh.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gjc.JE_CATEGORY_NAME = gjh.je_category
ANd gjc.language = USERENV(‘LANG’)
AND gjh.period_name = :PERIOD_NAME
AND gcc.segment1 = :segment1
AND gcc.segment2 = :segment2
AND gcc.segment3 = :segment3
AND gcc.segment4 = :segment4
AND gcc.segment5 = :segment5
AND gcc.segment6 = :segment6
AND gcc.segment7 = :segment7
)
GROUP BY period_name,
accounting_date,
posting_date,
journal_batch,
concat_segs,
jrnl_cat_desc,
journal_name,
journal_desc
Related Posts:
Accounting Reconciliation
Legal Entity Query in Fusion
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
Business Unit – Legal Entity – Ledger/ set of Books Relationship Query
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
