This GL Manual Journal Details query displays manually entered general ledger journal lines along with status explanations, providing insight into posting and validation issues.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Manual journal entries with status reasons
-- LEDGER AND BATCH INFO
GL.NAME LEDGER,
GJB.NAME JOURNAL_BATCH_NAME,
GJB.DESCRIPTION JOURNAL_BATCH_DESCRIPTION,
-- BATCH STATUS AND REASON
DECODE (
GJB.STATUS,
'+',
'Unable to validate or create CTA',
'+*',
'Was unable to validate or create CTA',
'-',
'Invalid or inactive rounding differences account in journal entry',
'-*',
'Modified invalid or inactive rounding differences account in journal entry',
'<',
'Showing sequence assignment failure',
'<*',
'Was showing sequence assignment failure',
'>',
'Showing cutoff rule violation',
'>*',
'Was showing cutoff rule violation',
'A',
'Journal batch failed funds reservation',
'A*',
'Journal batch previously failed funds reservation',
'AU',
'Showing batch with unopened period',
'B',
'Showing batch control total violation',
'B*',
'Was showing batch control total violation',
'BF',
'Showing batch with frozen or inactive budget',
'BU',
'Showing batch with unopened budget year',
'C',
'Showing unopened reporting currency period',
'C*',
'Was showing unopened reporting currency period',
'D',
'Selected for posting to an unopened period',
'D*',
'Was selected for posting to an unopened period',
'E',
'Showing no journal entries for this batch',
'E*',
'Was showing no journal entries for this batch',
'EU',
'Showing batch with unopened encumbrance year',
'F',
'Showing unopened reporting currency encumbrance year',
'F*',
'Was showing unopened reporting currency encumbrance year',
'G',
'Showing journal entry with invalid or inactive suspense account',
'G*',
'Was showing journal entry with invalid or inactive suspense account',
'H',
'Showing encumbrance journal entry with invalid or inactive reserve account',
'H*',
'Was showing encumbrance journal entry with invalid or inactive reserve account',
'I',
'In the process of being posted',
'J',
'Showing journal control total violation',
'J*',
'Was showing journal control total violation',
'K',
'Showing unbalanced intercompany journal entry',
'K*',
'Was showing unbalanced intercompany journal entry',
'L',
'Showing unbalanced journal entry by account category',
'L*',
'Was showing unbalanced journal entry by account category',
'M',
'Showing multiple problems preventing posting of batch',
'M*',
'Was showing multiple problems preventing posting of batch',
'N',
'Journal produced error during intercompany balance processing',
'N*',
'Journal produced error during intercompany balance processing',
'O',
'Unable to determine conversion rate to replicate journal',
'O*',
'Was unable to determine conversion rate to replicate journal',
'P',
'Posted',
'Q',
'Showing untaxed journal entry',
'Q*',
'Was showing untaxed journal entry',
'R',
'Showing unbalanced encumbrance entry without reserve account',
'R*',
'Was showing unbalanced encumbrance entry without reserve account',
'S',
'Already selected for posting',
'T',
'Showing invalid period and conversion information for this batch',
'T*',
'Was showing invalid period and conversion information for this batch',
'U',
'Unposted',
'V',
'Journal batch is unapproved',
'V*',
'Journal batch was unapproved',
'W',
'Showing an encumbrance journal entry with no encumbrance type',
'W*',
'Was showing an encumbrance journal entry with no encumbrance type',
'X',
'Showing an unbalanced journal entry but suspense not allowed',
'X*',
'Was showing an unbalanced journal entry but suspense not allowed',
'Z',
'Showing invalid journal entry lines or no journal entry lines',
'Z*',
'Was showing invalid journal entry lines or no journal entry lines',
'a',
'Showing insufficient access to ledger or segment value',
'a*',
'Was showing insufficient access to ledger or segment value',
'b',
'Showing insufficient access to generated accounts defined for ledger ',
'b*',
'Was showing insufficient access to generated accounts defined for ledger',
'c',
'Showing invalid balancing or management segment values for the ledger',
'c*',
'Was showing invalid balancing or management segment values for the ledger',
'd',
'Showing insufficient access to reporting currency or segment value',
'd*',
'Was showing insufficient access to reporting currency or segment value',
'e',
'Showing invalid balancing segment values for generated accounts',
'e*',
'Was showing invalid balancing segment values for generated accounts',
'f',
'Showing unopened secondary ledger period',
'f*',
'Was showing unopened secondary ledger period',
'g',
'Showing unopened secondary ledger encumbrance year',
'g*',
'Was showing unopened secondary ledger encumbrance year',
'h',
'Unable to retrieve accounts from chart of accounts mapping',
'h*',
'Was unable to retrieve accounts from chart of accounts mapping',
'i',
'Unable to determine journal effective date',
'i*',
'Was unable to determine journal effective date',
NULL
) JOURNAL_BATCH_STATUS,
-- JOURNAL HEADER INFO
GJH.NAME JOURNAL_HEADER_NAME,
GJH.DESCRIPTION JOURNAL_HEADER_DESCRIPTION,
GJH.STATUS JOURNAL_HEADER_STATUS,
-- JOURNAL SOURCE AND CATEGORY
GJST.USER_JE_SOURCE_NAME JOURNAL_SOURCE,
GJCT.DESCRIPTION JOURNAL_CATEGORY,
-- JOURNAL LINE INFO
GJL.JE_LINE_NUM JOURNAL_LINE_NUMBER,
-- ACCOUNT INFO
GCC.segment1 || '.' || GCC.segment2 || '.' || GCC.segment3 || '.' || GCC.segment4 || '.' || GCC.segment5|| '.' || GCC.segment6 GL_ACCOUNT,
DECODE (
GCC.ACCOUNT_TYPE,
'A',
'Asset',
'C',
'Budgetary (CR)',
'D',
'Budgetary (DR)',
'E',
'Expense',
'L',
'Liability',
'O',
'Owners Equity',
'R',
'Revenue',
NULL
) ACCOUNT_TYPE,
-- AMOUNTS
DECODE (
GJH.CURRENCY_CODE,
'STAT',
0,
NVL (GJL.ACCOUNTED_DR, 0) - NVL (GJL.ACCOUNTED_CR, 0)
) AMOUNT,
DECODE (
GJH.CURRENCY_CODE,
'STAT',
0,
NVL (GJL.ACCOUNTED_DR, 0)
) AMOUNT_DEBIT,
DECODE (
GJH.CURRENCY_CODE,
'STAT',
0,
NVL (GJL.ACCOUNTED_CR, 0)
) AMOUNT_CREDIT,
DECODE (
GJH.CURRENCY_CODE,
'STAT',
0,
NVL (GJL.ENTERED_DR, 0)
) FOREIGN_AMOUNT_DEBIT,
DECODE (
GJH.CURRENCY_CODE,
'STAT',
0,
NVL (GJL.ENTERED_CR, 0)
) FOREIGN_AMOUNT_CREDIT
FROM
GL_JE_CATEGORIES_TL GJCT,
GL_JE_SOURCES_TL GJST,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
GL_JE_BATCHES GJB,
GL_CODE_COMBINATIONS GCC,
GL_LEDGERS GL
WHERE
GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.ACTUAL_FLAG = 'A'
AND GJB.ACTUAL_FLAG = 'A'
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJL.LEDGER_ID = GL.LEDGER_ID
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME
AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME
AND GJST.LANGUAGE = USERENV ('LANG')
AND GJCT.LANGUAGE = USERENV ('LANG')
AND GJH.JE_SOURCE = 'Manual'
ORDER BY GJB.NAME ,
GJH.NAME ,
GJL.JE_LINE_NUM