This GL Journal Batches Information query displays journal batch details with balances and statuses for a specified accounting period to support financial reporting and control monitoring.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Retrieves journal batch details with balances and statuses for a specific accounting period.
-- LEDGER AND PERIOD INFO
GL.NAME LEDGER_NAME,
GP.PERIOD_NAME PERIOD_NAME,
GP.PERIOD_YEAR PERIOD_YEAR,
GP.PERIOD_NUM PERIOD_NUM,
GP.PERIOD_TYPE PERIOD_TYPE,
GP.ADJUSTMENT_PERIOD_FLAG ADJUSTMENT_PERIOD_FLAG, --Calendar period adjustment status.
GP.START_DATE START_DATE, --Date when the accounting period begins.
GP.END_DATE END_DATE, --Date when the accounting period ends.
-- BATCH HEADER INFO
GJB1.NAME BATCH_NAME,
GJB2.NAME PARENT_BATCH_NAME,
GJB1.DESCRIPTION BATCH_DESCRIPTION,
GJS.JE_SOURCE_NAME JOURNAL_SOURCE_NAME,
GJB1.DEFAULT_PERIOD_NAME DEFAULT_PERIOD_NAME, --Default accounting period for journal batch.
GJB1.ACTUAL_FLAG BALANCE_TYPE_FLAG,
-- STATUS & CONTROL INFO
GJB1.APPROVAL_STATUS_CODE APPROVAL_STATUS_CODE,
GJB1.STATUS STATUS,
GJB1.BUDGETARY_CONTROL_STATUS BUDGETARY_CONTROL_STATUS_CODE,
NVL(GJB1.CONTROL_TOTAL, 0) CONTROL_TOTAL,
-- Currency
GL.CURRENCY_CODE CURRENCY,
-- ENTERED BALANCES
NVL(GJB1.RUNNING_TOTAL_CR, 0) ENTERED_CREDIT,
NVL(GJB1.RUNNING_TOTAL_DR, 0) ENTERED_DEBIT,
-- ACCOUNTED BALANCES
NVL(GJB1.RUNNING_TOTAL_ACCOUNTED_CR, 0) ACCOUNTED_CREDIT,
NVL(GJB1.RUNNING_TOTAL_ACCOUNTED_DR, 0) ACCOUNTED_DEBIT,
NVL(GJB1.RUNNING_TOTAL_ACCOUNTED_DR, 0) - NVL(GJB1.RUNNING_TOTAL_ACCOUNTED_CR, 0) TOTAL_ACCOUNTED,
-- AUDIT INFO
PU.USERNAME CREATED_BY_USER_NAME,
TRUNC(GJB1.DATE_CREATED) DATE_CREATED,
TRUNC(GJB1.POSTED_DATE) POSTED_DATE
FROM
GL_JE_BATCHES GJB1,
GL_JE_BATCHES GJB2,
GL_JE_SOURCES GJS,
GL_PERIODS GP,
PER_USERS PU,
PER_ALL_PEOPLE_F PAPF,
GL_LEDGERS GL
WHERE
1 = 1
AND GJB1.PARENT_JE_BATCH_ID = GJB2.JE_BATCH_ID
AND GJB1.PERIOD_SET_NAME = GL.PERIOD_SET_NAME
AND GJB1.ACCOUNTED_PERIOD_TYPE = GL.ACCOUNTED_PERIOD_TYPE
AND GJB1.CHART_OF_ACCOUNTS_ID = GL.CHART_OF_ACCOUNTS_ID
AND GP.PERIOD_NAME = GJB1.DEFAULT_PERIOD_NAME
AND GP.PERIOD_SET_NAME = GJB1.PERIOD_SET_NAME
AND GP.PERIOD_TYPE = GJB1.ACCOUNTED_PERIOD_TYPE
AND GP.PERIOD_NAME = GJB1.DEFAULT_PERIOD_NAME
AND PAPF.PERSON_ID = PU.PERSON_ID
AND GJB1.CREATED_BY = PU.USERNAME
AND GJS.JE_SOURCE_NAME = ( SELECT NVL(MAX(JEH.JE_SOURCE), 'MANUAL')
FROM GL_JE_HEADERS JEH
WHERE JEH.JE_BATCH_ID = GJB1.JE_BATCH_ID
AND ROWNUM = 1
)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
ORDER BY GL.NAME,
GJB1.NAME,
GP.PERIOD_YEAR ,
GP.PERIOD_NUM ;