Reporting on Period-End Accruals Posted to General Ledger at Summary Level
In Oracle Cloud ERP, when accruals are posted to the General Ledger at a summarised level, the associated journal lines often display only the description “Journal Import Created,” making it difficult to trace back to the originating purchase order details. This can pose challenges during period-end validation or audit reviews when users need to understand the underlying transactions.
To address this, the following SQL query retrieves and exposes the key source details behind the accrual journals. It links the summarised journal entries back to their underlying Purchase Orders, including the PO number, line information, and the corresponding GL coding, providing full visibility into how the accrual was derived.
Period-End Accruals
SELECT Ledger.name AS LedgerName
, JrnlHdr.period_name
, TO_CHAR(JrnlHdr.default_effective_date, 'DD/MM/YYYY') AS AccountingDate
, GLCodeCombination.segment1 AS BalancingSegment
, GLCodeCombination.segment2 AS CostCentreSegment
, GLCodeCombination.segment3 AS AccountSegment
, JrnlSrcTransLang.user_je_source_name AS UserSourceName
, JrnlCatTransLang.user_je_category_name AS UserCategoryName
, ROUND(NVL(TransactionDist.unrounded_accounted_dr, 0), 2) AS AccountedDr
, ROUND(NVL(TransactionDist.unrounded_accounted_cr ,0), 2) AS AccountedCr
, PurchaseOrderLine.item_description AS PoLineDescription
, JrnlLine.description AS JrnlLineDescription
, JrnlBatch.name AS JrnlBatchName
, JrnlHdr.name AS JrnlHdrName
, PurchaseOrderHeader.segment1 AS PoNumber
, Supplier.segment1 AS SupplierNumber
, NVL(AltName.party_name, Party.party_name) AS VendorName
FROM GL_JE_LINES JrnlLine
, GL_JE_HEADERS JrnlHdr
, GL_JE_BATCHES JrnlBatch
, GL_LEDGERS Ledger
, GL_JE_SOURCES_TL JrnlSrcTransLang
, GL_JE_CATEGORIES_TL JrnlCatTransLang
, GL_IMPORT_REFERENCES GlImportReferences
, XLA_AE_LINES XlaLines
, XLA_AE_HEADERS Xlahdr
, XLA_TRANSACTION_ENTITIES TransactionEntity
, XLA_DISTRIBUTION_LINKS TransactionDist
, GL_CODE_COMBINATIONS GLCodeCombination
, POZ_SUPPLIERS Supplier
, HZ_PARTIES Party
, HZ_ADDTNL_PARTY_NAMES AltName
, CMR_RCV_DISTRIBUTIONS ReceiptDistributions
, CMR_RCV_EVENTS ReceiptEvents
, CMR_PURCHASE_ORDER_DTLS PurchaseOrderDetails
, PO_LINES_ALL PurchaseOrderLine
, PO_HEADERS_ALL PurchaseOrderHeader
WHERE JrnlLine.je_header_id = JrnlHdr.je_header_id
AND JrnlHdr.je_batch_id = JrnlBatch.je_batch_id
AND JrnlLine.ledger_id = Ledger.ledger_id
AND JrnlHdr.je_source = JrnlSrcTransLang.je_source_name
AND JrnlSrcTransLang.language = USERENV('LANG')
AND JrnlHdr.je_category = JrnlCatTransLang.je_category_name
AND JrnlCatTransLang.language = USERENV('LANG')
AND JrnlSrcTransLang.user_je_source_name = 'Receipt Accounting'
AND JrnlCatTransLang.user_je_category_name = 'Period End Accrual'
AND JrnlHdr.actual_flag = 'A'
AND JrnlHdr.status = 'P'
AND JrnlLine.code_combination_id = GLCodeCombination.code_combination_id
AND JrnlLine.je_header_id = GlImportReferences.je_header_id
AND JrnlLine.je_line_num = GlImportReferences.je_line_num
AND GlImportReferences.gl_sl_link_id IS NOT NULL
AND GlImportReferences.gl_sl_link_id = XlaLines.gl_sl_link_id
AND GlImportReferences.gl_sl_link_table = XlaLines.gl_sl_link_table
AND Xlalines.ae_header_id = Xlahdr.ae_header_id
AND Xlahdr.entity_id = TransactionEntity.entity_id
AND Xlahdr.balance_type_code = 'A'
AND Xlahdr.accounting_entry_status_code = 'F'
AND XlaLines.application_id = 10096
AND XlaLines.application_id = TransactionDist.application_id
AND XlaLines.ae_header_id = TransactionDist.ae_header_id
AND XlaLines.ae_line_num = TransactionDist.ae_line_num
AND TransactionDist.source_distribution_type = 'PERIOD_END_ACCRUAL'
AND ReceiptEvents.accounting_event_id = ReceiptDistributions.accounting_event_id
AND TransactionDist.source_distribution_type = ReceiptEvents.entity_code
AND TransactionDist.source_distribution_id_num_1 = ReceiptDistributions.cmr_sub_ledger_id
AND TransactionDist.calculate_g_l_amts_flag = 'N'
AND ReceiptEvents.event_class_code = 'PERIOD_END_ACCRUAL'
AND ReceiptEvents.cmr_po_distribution_id = PurchaseOrderDetails.cmr_po_distribution_id
AND PurchaseOrderDetails.active_flag = 'Y'
AND PurchaseOrderDetails.po_line_id = PurchaseOrderLine.po_line_id
AND PurchaseOrderDetails.po_header_id = PurchaseOrderHeader.po_header_id
AND PurchaseOrderDetails.vendor_id = Supplier.vendor_id
AND Supplier.party_id = Party.party_id
AND Party.party_id = AltName.party_id(+)
AND AltName.party_name_type(+) = 'PHONETIC'
AND AltName.preferred_flag(+) = 'Y'
AND AltName.status_flag(+) = 'A'
Need some assistance?
Ready to get more value from your Oracle data? Whether you’re using Oracle Transactional Business Intelligence (OTBI), BI Publisher, Fusion Data Intelligence (FDI), or a third-party analytics tool, we can help you turn complex data into clear, actionable insights. Contact us today to explore how our Business Intelligence and Analytics solutions can empower smarter, faster decision-making across your organisation.
