This GL Journal By Document Number Report query displays document sequencing and posting details for GL journal batches, headers, and categories, enabling tracking of posted journal entries for audit and reconciliation.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieves posted GL journals with document numbers and key details
--Batch & Header Info
GJB.NAME JOURNAL_BATCH_NAME,
GJH.NAME JOURNAL_HEADER_NAME,
GJH.POSTED_DATE POSTED_DATE,
--Category & Status
GJC.USER_JE_CATEGORY_NAME CATEGORY,
GL.DESCRIPTION POSTING_STATUS,
--Amounts
GJH.CURRENCY_CODE,
GJH.RUNNING_TOTAL_DR DEBITS,
GJH.RUNNING_TOTAL_CR CREDITS,
--Document Sequence Info
FDS.NAME DOCUMENT_SEQUENCE_NAME,
GDSA.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,
GDSA.CREATION_DATE AUDIT_CREATION_DATE
FROM
GL_JE_CATEGORIES GJC,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_DOC_SEQUENCE_AUDIT GDSA,
FND_DOCUMENT_SEQUENCES FDS,
GL_LOOKUPS GL,
GL_LEDGERS GLU
WHERE
1 = 1
AND GL.LOOKUP_CODE = GJH.STATUS
AND GJC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.DOC_SEQUENCE_VALUE = GDSA.DOC_SEQUENCE_VALUE(+)
AND GJH.DOC_SEQUENCE_ID = GDSA.DOC_SEQUENCE_ID(+)
AND GDSA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
AND GLU.LEDGER_ID = GJH.LEDGER_ID
AND GJH.STATUS = 'P'
AND (
(GJH.PARENT_JE_HEADER_ID IS NULL)
OR (NVL (GJH.JE_FROM_SLA_FLAG, 'N') = 'Y')
);